In this issue

Featured Contents


Featured Script

SQL Backup Pro logo Gold Community Choice Award for SQL Backup Pro
Try award-winning SQL Backup Pro for faster, smaller, fully verified SQL Server backups. Download a free trial now.
SQL Source Control logo Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.
SQL DBA Bundle Logo ‘Beating Backup Corruption’
The DBA Team are back, revealing more of their ‘Top 5 Hard-earned Lessons’. Lesson two is out now! Learn to beat backup corruption.

In This Issue

Moving Database Files in SQL Server

A short article that talks about the isues with moving SQL Server database files around on your storage subsystem. More »

SQL Saturday #198 - Vancouver

SQL Saturday comes back to Vancouver, BC on Feb 16, 2013. Come join in if you can. More »

Move all SQL Server indexed views to a new filegroup

As databases grow, it often becomes necessary to add new I/O paths to accommodate the growing space. Even without growth that requires this scale, it can still be useful to utilize multiple I/O devices to spread out the load. One way that you can make optimal use of new drives is to add a filegroup to the database and move certain objects (say, all of your indexed views) to the new filegroup. More »

From the SQLServerCentral Blogs - Duplicate rows in a table

I recently got the following email: “I just keyed in two rows into a table with all identical column values. How... More »

Editorial - Data Freedom and Regulation

The HIPAA laws passed in the US to provide for better privacy an security of medical information seem to be a joke in many of the situations in which I've dealt with medical providers. It almost seems like signing a HIPAA acknowledgment form is a formality and as patients, we should understand that HIPAA provides for standard requirements and protections for our data. However I'm not sure that's the case. 

This article talks about the HIPAA laws being a floor, not a ceiling, and a patchwork of laws in various states superceed what HIPAA requires. However in doing so, they create inconsistent regulations and rules that people struggle to understand, and with which technology cannot keep up. I'd take issue with the comment that "Digital systems to move information need simplicity". It's not true. Our digital systems are very adept at handling exceptions and variable routing and security when they are programmed to do so. The problem is ensuring the people writing the code understand all of the rules for the exceptions.

The article talks about the approach Hawaii has taken, in scrapping older laws and simplifying them to comply and expand the HIPAA requirements so that providers and patients can understand how to handle data. I suspect that few governments will take this approach, but it's precisely what's needed, in all fields, for those of us working with data to build systems that can not only comply with the laws, but also protect data in a secure manner.

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

Advertisement: If you are looking to speed up your development process and reduce mistakes with Continuous Integration, you might be interested in these white papers on automated deployment and CI for databases from Red Gate Software. It talks about how you can set up a process using various tools.

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at 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

You can also follow Steve Jones on Twitter:

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

Question of the Day

Today's Question:

What will the outcome be after running all the following statements as 1 batch?

-- Statement 1
Create Table #TempQoD
       RowId Integer
     , Question varChar(max)
     , Answer varChar(max)
     , PublishDate Date

-- Statement 2
       1 As [RowId]
     , 'True or False: The HAVING clause is the 4th phase of logical query processing?' As [Question]
     , 'True: 1-FROM, 2-WHERE, 3-GROUP BY, 4-HAVING, 5-SELECT, 6-ORDER BY.' As [Answer]
     , '15 Jan 2013' As [PublishDate]

-- Statement 3
Select * From #TempQoD;

Drop Table #TempQoD;

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

Yesterday's Question of the Day

I open up the default Thesaurus file for my language on my SQL Server 2008 instance and only add this code after the  first expansion set.




I save the file, reload it with sp_fulltext_load_thesaurus_file, and execute a query, but my thesaurus file does not appear to be returning any results. What could be wrong?

Answer: The entire thesaurus file is commented out.

Explanation: By default, thesaurus files are commented out, meaning that any entries in them will not be used by the full text search in that language. Removing the comment lines will allow this to work.


» Discuss this question and answer on the forums

Performance Tuning with SQL Server Dynamic Management Views

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

Featured Script

Tables and rows listing

Simple script which create a list of all the tables and rows of the selected DB. More »

Database Pros Who Need Your Help!

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

SQL Server 2005 : Administering

Can't start SQL Server service as Network Service - I have a development server that is running SQL Server 2005. I was asked to install a new instance for...

SQL Server 2005 : Backups

Backup plan confusion! - Please consider the following setup: 5 SQL Server instances writing backup files to the same NAS device 7.5TB capacity [b]Backup Schedule (Fortnightly...

SQL Server 2005 : SQL Server 2005 General Discussion

Import and Export Wizard - remove double quotes from csv data - I am frequently given csv files in following format: [quote]"Id", "Col1", "Col2", "Col3" "1", "x1", "y1", "z1" "2", "x2", "y2", "z2"[/quote] The column names...

SQL Server 2005 : SQL Server 2005 Integration Services

Move Multiple tables from SQL Source to SQL Destination - Hi friends, This is my first post in SSC, Im new to SSIS ., Scenario : SOURCE SQL DB NAME 'A' DESTINATION...

SQL Server 2008 : SQL Server 2008 - General

Bulk insert messing up fields - GO Use test DROP TABLE PDTEST create table pdtest ( CustID INT IDENTITY(1,1), work_phone_extension varchar(5), residential_postal_or_zip_code varchar(30), residential_street_address_line_1 varchar(30), residence_phone varchar(17), work_phone varchar(17), name_part...

SQL Server Command line utility for windows XP - Hello, I am trying to run a job lying on a SQL Server(2012) from a user machine where database is not...

What are best practices for setting transaction log size for tempdb? - Please some one help me ..... :-)What are best practices for setting transaction log size for tempdb?

Report export to excel not working - Hi experts, I have a report in SSRS. It has grouping and is working fine. When I export to excel...

Change value of column in last row when doing insert - [code="sql"] DECLARE @LastOne bit = 0 CREATE TABLE #tblTasks ( TaskID int, Task varchar(50), LastOne bit, ) GO INSERT INTO #tblTasks (TaskID, Task, LastOne) SELECT(TaskID, Task,...

Full Backup "perform checksum before writing to media" - Hi, Guys. i want to ask one thing about full, Differential & Transcriptional backup. Can we select the option "perform checksum before writing...

Merge Replication - In my environment, there is a merge replication configured . In one database three merge replications is configured. The database has xml...

Error 17836 simultaneous occurance on two separate instances - Greetings fellow SQL enthusiasts. I was alerted to this error on both SQL 2008 and 2005 instances and just beginning...

Joins - CREATE TABLE TableKeys ( BusinessKeys NVARCHAR(MAX)) INSERT INTO TableKeys (BusinessKeys) SELECT SourcTable.ColumnA+','+ SourcTable.ColumnB+','+ SourcTable.ColumnC+','+SourcTable.ColumnC FROM SourcTable AND I perform a join Like...

Query for find bdetails for Job Name - Enabled - Schedule of job - I want to write a query to find below details of jobs Job Name - Enabled - Schedule of job if job...

What are best practices for setting transaction log size for user databases and what is for tempdb? - What are best practices for setting transaction log size for user databases and what is for tempdb? Can some one please...

How Check string of Boolean expression is return true or false - Hi Guys, I have a requirement in my project is how to check string of boolean expression is true or...

SSIS import multiple csv files by name - Hi, I have an ever increasing number of files in a single directory (will probably end up with hundreds). There's 5...

TSQL Join query help - Table does a read and write that are the same div_time and Unit however the filled time changes . The Div_time...

Change filegroups for existing table from Primary to FG_JJ - SQL Server: SQL 2008 r2 1) Is there a way to change the FileGroup for a sql table having 16m records...

Linked server using MySQL ODBC 5.1.8 - Hi , I am trying to add a linked server using the MySQL ODBC 5.1.8 but I am always getting the...

SQL Server 2008 : T-SQL (SS2K8)

T-SQL vs SQL differences - This is a relatively pedantic but necessary question as I have seen differing explanations on the subject. If I say I...

Calculating a Moving Average - Hi, I have a need to calculate a moving average. Ultimately, I need to convert the Select statement into an Update...

Maximum Concurrent Users in a day - Hi all I have a table which contains login and logout times for a large set of users, and we are...

Update a table based upon CTE data - I've been playing around with CTE's and when I run the code below it executes instantaneously.[code="sql"]; WITH PaymentsTxn AS ( SELECT...

UNION vs OR --> NP-Complete Problem - I have recently read about this in the below link. I just heard about "NP-Complete Problem" first time :) [quote] From Joe Celko... This...

SQL Server 2008 : SQL Server Newbies

Separate Groups by Sequential ID - Hi All, First time posting to SQLservercentral. I have a table called DataLogging that looks like the picture attached. [img][/img] I would like...

How to Add Leading Zeros - Hello, I am a novice to SQL, I can do the simple queries but at the moment I struggle with writing...

SQL Server 2008 : SQL Server 2008 High Availability

Replicate a new table without taking a snapshot - I have a 350 GB table that is replicated, and I need to rename. This will require replication to be...

side by side upgrade-replication cut over - We are planning to do a side by side upgrade of a prod server. We have replication configured on it....

SQL Server 2008 : SQL Server 2008 Administration

Delete Trigger to capture Login, Application, etc - I have a situation where a developer is inserting a large number of records into a table. They performed the...

Backups to a Share Drive - I execute the Job and it succeeds. However I go to the location that I specified as the backup location...

SQL server performance condition Alert - HI experts Do any one knows a good article explains in detail how to use sql server performance condions ALERTS Thanks lot

Transaction Log Backup - Our company has never backed up the transaction log file, and is now about 143 gig, with about 35 gig...

To Re-index all indexes for a table. - Hi All, I have to do re-index of all the indexes on a particular table, however, when I go to database...

Best all-round monitoring tool for - Morning all. I've been asked to garner the opinions of the masses as to a best-of-breed monitoring solution for our SQL2008...

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 : Employers and Employees

BI Specialist vs SQL Developer - Hi all. I was recently offered for SQL developer position and BI specialist position, and I would like to get... : Anything that is NOT about SQL!

Starting a new job on Monday, and Sharepoint is involved, need suggestion on managing the SQL side. - Yep, I got a new job! :w00t: The scary part, I'm expected to help the Sharepoint team with the SQL side...

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

SSRS 2008R2 EXPRESSION - I want to add this expression in SSRS 2008R2 For [b]Column Heading[/b] If {Reportname.value} = 'ABC' And {Reportname.value} = 'S' Then 'Amount1' Else If...

Data Warehousing : Integration Services

Separate ETL server - I have recently inherited responsibility for managing a small data warehousing team, using SQL Server 2008 R2. My predecessor, on...

Inremental loading SSIS- 200-300 tables - Hi Guys, my project started 2 months ago, and alalready'i'mransferring over 100 tables on each process i run to the...

Output multiple SQL query result into excel using SSIS - Hi All, I am new to SSIS and am currently working on a requirement as follows: We have 30 sql scripts which...