SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

Data Preservation

Maintaining data across time isn't something many of us think about. We work with data in the here and now, and in the database world, we typically only need to recover or restore data from a short window. Like most of you, I would usually plan on recovering data that's only a few days old. Being forced to restore a database from two weeks on any of my systems would make me cringe. It certainly would be embarrassing for me personally if it were my fault I couldn't restore to a point in time that was more recent than that.

In planning to recover our systems, we typically know the versions of software we have to recover from, and we can easily re-download copies of SQL Server or the patches we need. Most of us are dealing with SQL Server 2000 or later, which is good since those are the only versions still documented on MSDN. If you need SQL Server 7.0 or SQL Server v6.5 documentation, I hope you have copies.  The same goes for the media. You can still download SQL Server v6.5, and SP5, but if you needed SP3, it isn't easily available. I ran into that situation about 10 years ago, and we had to make a special request through our TAP manager to get someone in Redmond to dig up a copy.

In some ways it might not be important to worry about long term storage. Most of us will end up transferring our data to newer systems (and formats) over time. As we upgrade SQL Server, our databases move along to newer formats, or we abandon them because they are no longer needed. That's fine for some data, but not all.

Long term archival and storage is a challenge, as you can see in this short look at how old films are maintained. It just touches the edges of what's being done, and doesn't address costs. Plenty of old films have been lost forever, and perhaps that doesn't matter, but it does concern me. I have thousands, maybe tens of thousands of digital images. While I love the ease with which I can share them with family, and make extra copies, I am worried that perhaps the lack of a physical copy means my great-grandchildren will struggle to find evidence of my generation if there is a catastrophe or storage formats change.

This is one area of our industry in which we have a lot of maturing to do, and I hope that we can come up with some new ideas for maintaining our data for the long term, across not months or years, but decades or centuries.

Steve Jones from SQLServerCentral.com

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


Video and Audio versions

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.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Steve Jones

Windows Media Video ( 18.9MB) feed

MP4 iPod Video ( 22.0MB) feed

MP3 Audio ( 4.5MB) feed

Feeds are available at iTunes and Mevio

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


ADVERTISEMENT

Easy release management

Deploy your .NET apps, services, and SQL Server databases in a single, repeatable process with Red Gate’s Deployment Manager. Get started now

ADVERTISEMENT
SQL Developer Bundle

12 must-have SQL Server tools

The award-winning SQL Developer Bundle contains 12 tools for faster, simpler SQL Server development. Download a free trial.

Fundamentals of SQL Server Replication eBook

Get to grips with SQL Server replication

In this new eBook Sebastian Meine gives a hands-on introduction to SQL Server replication, including implementation and security. Download free ebook now.

Featured Contents

 

Lessons Learned from Six Years of Agile Database Development

Additional Articles from SimpleTalk

Alex Kuznetsov describes the agile principles, techniques and tools that allowed his development team to make frequent database refactoring a reality, without disrupting users. He explains how this allowed his team to apply an iterative, evolutionary approach to the design and development of their databases, as well as applications. More »


 

SQL Monitor Metric: Processes in a runnable state (scheduler query)

Press Release from SQL Monitor Metrics

Use this script in SQL Monitor to determine the general load on the operating system you can get a count of the processes that are in a runnable state. This value will go up and down as various systems run on the operating system. It’s only a measure of load on the system and won’t indicate the cause of problems, but will show you pressure on the system. More »


 

Helping Streamline Log Shipping Failovers

Micah Nikkel from SQLServerCentral.com

Using Log Shipping? Looking for a more automated way to fail over during a disaster? Read on... More »

Question of the Day

Today's Question (by Carlo Carlo):

In the database QOTD data will reside in the 'PRIMARY' filegroup and indexes on an alternate filegroup 'QOTD_index'

What are the result of the three queries below?

Note: Assume all queries succeed.


CREATE DATABASE QOTD ON (NAME = QOTD,filename='c:\temp\QOTD.mdf')
  , FILEGROUP QOTD_index (NAME = QOTD_idx,filename='c:\temp\QOTD_idx.mdf')
  LOG ON (NAME = QOTD_log,filename= 'c:\temp\QOTD.ldf')
go
USE QOTD
create table Tab1(i int) ON 'PRIMARY';
INSERT Tab1
        SELECT a.object_id
            FROM sys.objects a;
ALTER TABLE Tab1 
 ADD k INT IDENTITY
-- Query 1
DECLARE @id INT = OBJECT_ID('Tab1')
EXEC sys.sp_objectfilegroup @objid = @id
GO
-- New index
create unique CLUSTERED index idx_Tab1 on Tab1(k,i)
    ON 'QOTD_index';
-- Query 2
DECLARE @id INT = OBJECT_ID('Tab1')
EXEC sys.sp_objectfilegroup @objid = @id
-- Query 3
SELECT s.name
    FROM sys.indexes i
        JOIN sys.data_spaces s
        ON i.data_space_id = s.data_space_id
    WHERE i.name = 'idx_Tab1'

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


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Filegroups.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

ADVERTISEMENT

SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups

SQL Server 2012 brought in some great new features and one of the important ones is scalability and performance via AlwaysOn. AlwaysOn is a superset feature and is a combination of many things you will learn about. As the language suggests, this technology achieves a SQL Server infrastructure that can be "always on". For businesses that run 24x7 downtime means the loss of business. This type of risk is out of the question for these businesses. This book discusses in detail the concepts of SQL Server AlwaysOn starting from the basics.

 

Yesterday's Question of the Day

Yesterday's Question (by Steve Jacobs):

Given you have the following table:

create table #temp1
(
i int identity(1,1),
a numeric(8,2)
)

Your values in this table are
1,11
2,42
3,31
4,NULL
5,10
6,10.5

What can be added to order column 'a' in ASC order in the most efficient manner (Select all that can be applied)? 

Assumptions:

  1. column 'a' will allow duplicates.
  2. A select statement is used to return values (Select a from #temp1)

(Please choose 4)

Editor: This is an interesting question, however the only real way to guarentee this is to include an ORDER BY in the SELECT query. The other options to select can help this perform efficiently, but do not guarentee ordering

Please select 1, 2, 5, 7 as your answers and read the discussion.

Answer:

  • CREATE CLUSTERED INDEX IDX_A ON #temp1(a)
  • CREATE UNIQUE CLUSTERED INDEX IDX_A ON #temp1(a,i)
  • CREATE NONCLUSTERED INDEX IDX_A ON #temp1(a,i)
  • Add 'Order By a ASC' to select statement

Explanation:

Answers:

CREATE  CLUSTERED INDEX IDX_A ON #temp1(a)

CREATE  UNIQUE CLUSTERED INDEX IDX_A ON #temp1(a,i)

CREATE  NONCLUSTERED INDEX IDX_A ON #temp1(a,i)

Add 'Order By a ASC' to select statement

I have seen where indexes return the resultset not in the desired order (say the 100th or 1000th time you execute the query (you inserted new records, etc.)... "cached result").  This is one of the reasons most developers choose to use ORDER BY (due to its simplicity and not a full understanding of indexes) but this can be a big performance hit if the the column or columns are not indexed properly and you are dealing with very large data sets.

What I have seen to work most effectively is using a combination of both (Index (a composite index (with the column you wish to sort first) or Clustered index) and Order By). 

 NOTE:  Even though I have an option (or two) to index a field that has the ability to allow NULL values, it is not always wise to index a column that allows NULL values for this reason (just 1 of many):

The more the rows are selective (unique) the more likely the use of indexes, thus many NULL values will cause the engine to avoid the index.

But alas, there are "good" reasons to index NULL values (filtered indexes (new in SQL Server 2008) but this is more of an article discussion rather than a "question").

Indexes and Data Table Storage:  http://technet.microsoft.com/en-us/library/ms190457.aspx


» Discuss this question and answer on the forums

Featured Script

Create a Comma Delimited CSV File from Any Table/View with Debugging

Paul Andrew from SQLServerCentral.com

Standard usage:

  • Create the stored procedure in your database containing the objects you require CSV files for.
  • Execute the procedure passing the required parameters for the source object schema and object name. Plus the destination path for the generated file. Ensure you have write permissions to this location, the BCP utility supports local drives on your SQL Server and UNC paths only.

Optional parameters:

  • If you require a text qualifier in your CSV file this can be included as a parameter. For example: "
  • Setting the debug parameter to 1 will print the dynamically created SQL strings plus the BCP command output.

Note; if you object contains complex data types that SQL Server cannot implicitly, for example sql_varient please create a view of the objects to handle this separately.

Happy CSVing.

Thanks for looking.

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 2014 : Administration - SQL Server 2014

SJ Test - What is this worked for me featre?

Fusion-IO or Nimble? - We are finally setting up everything for a massive migration of all 2005 systems to 2012 with a "rollover" upgrade...

How many GB of memory does 1 database take - Hello - I need to take each database off of a physical instance and give memory, cpu specs of what each...

Pro Active DBA - Hi Guys, I've recently been appointed to maintain my companies internal DB's. Must also stress the point that the DB's are...


SQL Server 2014 : Development - SQL Server 2014

Query performance - Some of developers have made modifications to database structure like indexes and later observed that the performance of queries have...


SQL Server 2012 : SQL 2012 - General

Restore database issue - Hi friends, I'm trying to import customer's SQL 2005 database(database.bak) onto our SQL Server 2012 server. I right clicked on...

Why SQL server didn't see my Table definition? - Hello I declare a table as "@UserMidListTable" and I use it in inner join [code="other"]declare @string nvarchar(MAX) declare @UserMidListTable Table (Id Int,Mid...

UNICODE problem using Attunity 2.0 connection to Teradata - Hi, I'm trying to connect in SSIS to a Teradata 14.00 to get data via the Attunity driver 2.0. (using TPT...

Cannot restore database. Cannot get exclusive accesss - While restoring database in sql server 2012, i am getting the following error in sql server 2012 "Exclusive access could not...

Does LocalDB on Pen drive has Traces on Desktop? - I have desktop application using SQL Server LocalDB as database. Application is installed on desktop and Database is on Pen...

Problem SSIS 2012 using Teradata Destination - Hi, I installed Attunity SSIS Terdata Adapter Version 2.0 on my machine (SQL Server 2012 EE), downloaded from the Microsoft page. I...


SQL Server 2012 : SQL Server 2012 - T-SQL

Dynamically create a sql script only selecting columns where there is data - Morning, Thanks for your help in advance, very much appreciated. I have created some dynamic sql to check a temporary table that...

SSIS package backup of sharepoint - Hi all, Hi was assigned a project to make a backup of data from 3 lists in a sharepoint with the...

Find out if the whole column of data in a table is empty - dynamic sql creation - Morning, Thanks for your help in advance, very much appreciated. I have created some dynamic sql to check a temporary table that...

Question About Trapping Attempt to Insert Duplicate Record in Stored Procedure - I have the following stored procedure (shown at the bottom of this posting). It attempts to do a simple insertion...

insert into table execution of a stored procedure - Hello I have this table [b]declare @UserMidListTable Table (Id Int,Mid int,ValueMid int,CatParent int, [Enabled] int,LastUpdate Datetime,Company nvarchar(max))[/b] I want to fill it with...

Need a 2nd opinion on a query - I had a collegue write the following query. He got defensive when I asked what he was trying to accomplish...


SQL Server 2008 : SQL Server 2008 - General

Report Builder 3.0 Fixed Layout Challenge/Issue/Question - I have scoured the 4 [million] corners of the interweb and found nothing that seems to address the very painful...

Determine who SQL Server is licensed to - I am beginning to think this may be unanswerable (is that a real word?)... We have a company which was acquired....

Best way to monitor datafile and log file grow - Hi, what is the best way to monitor datafile and lofile grow ? With custom scripts ? Does perfmon can do that ? regards C

SSIS Package error - I just make a SSIS package and i get the following error. How can i fix it? It works before...

SSRS is it possible to insert custom line between the group list? - Hi, I have simple report but user wants to add some title that kind looks like parent group, is it possible...

rebuild index and index defrag failing - Rebuild index as well as index defrag jobs are failing with the following error message. How do I fix them? Executing...

Restriction on Primary key - There is a restriction that we must have only one primary key per table. We can have composite primary key...

Database mirroring - Dear All, I have to take a mirrored database 'offline' but I would need to bring them back on line at...

.bak file larger than .mdf file - I have a peculiar situation. There are several user databases on the instance, all other db's are backing up fine....

job is failing - one of my agent job is showing in suspended state and prompt of giving error Unable to start execution of...

Question about Ranking - Hello Please can someone help me out. This is an example of my data Service Date ===== ============== 400 01/02/13 13:53:08 400 01/02/13 15:22:06 400 01/02/13 16:45:54 400...

Forced Parametrization vs Optimize for Ad Hoc Workload - We have a few databases that run almost exclusively non-parametrized queries. A significant part of these queries is repeated often...

regarding automatic backup job - Hi all, I want to take automatic backup job for one of my database which is having 25gb data. i want to...

Need your help...SQL Developer interview questions..pls - Hi All, I am working in a small company having 2 years of experience .. But I am into Production Support..I am...

Make a new replication with exsist table. - Hello SQL Guru's, Im trying to make a replication with a exist table. When I make a replication its always drops...

sql server search address - I am trying to create search facility within t sql to be able to search a large table containing addresses,...

RCSI - Read Committed Snapshot Isolation - Hi, Our ERP is very badly design... We have long running transactions that block simple SELECT.. If I activate the RCSI on...

collection_set_3_upload job failed - Hi Guys, I have a job which run from Data collector and failed with this error message Name of the job...

Unique XP_Cmdshell issue. - Hi all, I am facing unique issue haven't experienced this kind of a issue so far. 1) I am running a...

264] An attempt was made to send an email when no email session has been established - Hi 264] An attempt was made to send an email when no email session has been established I see following error...

Need to fill the Gaps with previous value - Hi experts, I have a scenario to fill in the GAPS between the dates with previousdate+1 day. here is the table DDL...

2 MDF FILES WITH THE SAME NAME ? - I was just moving my mdf and ldf files from the c drive to d drive when i came across...


SQL Server 2008 : T-SQL (SS2K8)

Designing for Performance and Integrity but have a Tran Problem - I have a DB that grows like crazy. This was a design decision I had nothing to do with, but...

hat is wrong with this insert statement? - There is a syntax problem with the third column b.id and the last ')'. But I cant figure out why. Can someone...

Identify Duplicate Records according to Multiple Criteria - Hi There! I have an Employee table with Columns: EmpID | FirstName | MiddleName | LastName | SSN | BirthDate | HireDate | City | Zip [b]Requirement:[/b] I have to identify...

Display data in a Table - Goal: Display the data of xml in a customized table. The requested table list will be [code="vb"] user firstname lastname hour projectname sex ------------------------------------------------------------------------- userTime...

Enter user-defined variables at runtime - Hi all, I am writing a simple script to allow developers to create database snapshots for the purposes of code-testing. This is...


SQL Server 2008 : SQL Server Newbies

SQL Server 2008 Index Maintenance Plan failing - I have two SQL Server 2008 instances that are having issues with SQL Server Maintenance plans that "Rebuild" indexes on...

Gathering Information - I am a newbie working as a DBA and wanted to pull together some information about my environment. I created...

part of database backup - Hi, Is it possible to backup part of database. i have 2 yrs data in my database can i backup last...


SQL Server 2008 : Security (SS2K8)

Antivirus on large SQL Server instances - All, I'm having an issue where my current antivirus vendor is having issues when scanning my larger sql server machines....

Help on Audit Specifications - My questions on Audit Specifications are: Can it be setup to exclude capturing specific logins? Example we have login SA1...


SQL Server 2008 : SQL Server 2008 High Availability

Transactional Replication with growing commands and transactions - I have setup transactional replication at a customer site with SQL 2008 R1 SP1 Enterprise Edition residing on Windows 2008...


SQL Server 2008 : SQL Server 2008 Administration

SQL SERVER AUDIT - We have setup an audit on one of our prod server and collect the audit files on a drive. We...

how to see Windows server information - Hello, I am seeking advice from experienced SQL Server administrators who have had to deal with administering SQL Servers when...

How to Monitor the perfmon for remote server - Hi All, I need some information regarding Perfmon. i have configured some counters using perfmon on production server. but here i want...

Job Activity Monitor will not open -- reason? - On my local installation of the SQL Server Management Studio for SQL Server 2008 R2 everything works fine [including the...

How to access the database if I am not an administrator? - Hi All, I would like to access to the database even though I am not allowed to be an administrator. Is there...

Database restoring,,,,, since 20 hrs - Hi, i have 25 GB database backup file. I just restored it with a different database name. after starting restoring process...


SQL Server 2005 : Administering

SQL Database Mail Failure - Event Type: Error Event Source: DatabaseMail Event Category: None Event ID: 0 Date: 29-11-2013 Time: 14:08:30 User: N/A Computer: W6000 Description: 1) Exception Information =================== Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException Message: There was an error on the connection. Reason: A network-related or...


SQL Server 2005 : SQL Server 2005 Integration Services

How to convert a CSV file into XML? - I have a large CSV file that I want to export into XML format. How can I do this using...

The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. - Hi, I am trying to transfer the data from flat file to sql server.When I am running the package on local...


SQL Server 2005 : SQL Server Newbies

Working of the function - I have an existing function on my DB which is used for date manipulations. Please explain how this function works. [code="sql"] ALTER...

DBCC CHECKDB - How does DBCC CHECKDB works? What is the expected time to finish if the database size is about 800 GB?


Reporting Services : Reporting Services

How to set your own ssrs default parameter - I am using a SSRS Parameter for my reports. The parameter is populated by a Dataset - sql query. [code="other"]select ProductName from Product...

SSRS Query Data Retrival Time - For a SSRS 2008 report I have noticied a couple of strange perormance issues. 1) When capturing a profile of the...

How to pass credentials to open FTP - I need to fetch images via FTP to a folder, but the folder is protected by password. How do I...

Internet Explorer cannot display the webpage - Hello, my user has a SQL Server Reporting Service problem. Windows 2003 Standard x64 Edition Physical memory : 33GB SQL Server 2008 Standard Edition,...

SSRS Challenge: Toggle column visibility - Say I have a simple table in my database. it has just four columns: [code="sql"]foo, bar, foe, fum[/code] Say that the data...


Reporting Services : Reporting Services 2008 Administration

Report Model Security - Hi All, We have a SSRS Report Model published to multiple users across our business (Around 25 users from 5 different...


Reporting Services : Reporting Services 2008 Development

Store Proc - I have a store proc which not only creates the table but inserts data in that table everytime it executes. So...


Programming : XML

updating rows with xml - Hi, I have a loop built which selects groups of records out based on different product groups. The results are returned as...


Programming : Service Broker

Moving databases to another instance using Service Broker? - Hi, We have a couple of production instances with many databases on SQL Server 2008 Std. I want to implement an automated...


Data Warehousing : Integration Services

Easy Question: Organizing Data Flows - Since I'm new to this and data warehousing, I'm not sure what is the best way to do what I'm...

How to read UniqueIdentifier output parameter from a stored procedure via OLE DB - I have a stored procedure that takes some input parameters and returns a GUID as an output parameter. I want...

File exists for multiple specific filenames in folder - Hi. In a SSIS package I need to first check that all files exist that I need before I start processing...

Using 64-bit ODBC Driver - I need to pull data from a 64-bit Progress database using a 64-bit ODBC driver (at this time it seems...


Data Warehousing : Analysis Services

Account Dimension - Net Income - I have created a cube for Financial reporting using the AdventureWorks 2008R2 DW as a guide. I've defined my GL...


Database Design : Relational Theory

Database without ANY primary keys - Hi all, I work with a database that has no primary keys defined on any tables. As a customer's consultant I...


SQLServerCentral.com : Anything that is NOT about SQL!

KB Knowledge Store - I would be instrested to hear from fellow SQL DBA's BI etc.. Where do you store your notes ? By notes I...

Fantasy football 2013 - I renewed the league, you should be getting an email soon. At the moment, there are no open spots, but...

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


SQL Server 7,2000 : T-SQL

LIKE - finding ']' or '.' in data - Hi, In the code below I am trying to find all of the values with '.123' or ']123' within them. Obviously...


Career : Certification

70-461 - Done, but more difficult than expected - Just passed the 70-461 SQL Developer Exam Today. This exam is much harder than I expected. Its quite syntax heavy, and asks...


Career : Resumes and Job Hunters

Looking for a Sr. SQL DBA out of SF - Contact: Amber.Richard@staffmark.com Phone: (925) 969-4433 LinkedIn: [url=http://www.linkedin.com/in/amberrichard/]www.linkedin.com/in/amberrichard/[/url] Searching for a Senior level SQL DBA. This is a F/T or contract-to-hire position. SQL...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com