In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Pro wins Gold Award SQL Backup Pro wins Gold Community Choice Award
Find out why the SQL Server Community voted SQL Backup Pro 'Best Backup and Recovery Product 2012'. Get faster, smaller, fully verified backups. Download a free trial now.
 
Deployment Manager NEW! Never waste another weekend deploying
Deploy SQL Server changes and ASP .NET applications fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.
 
SQL DBA Bundle ‘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

Stairway to SQL Server Indexes: Level 3, Clustered Indexes

Now that we've seen the basics of indexing, and taken a deeper dive into Nonclustered Indexes, this Level will focus on searching the table, which will, in turn, will lead us to a discussion of clustered indexes. More »


Free eBook: SQL Server Backup and Restore

You can download a free eBook from SQLServerCentral and Red Gate software on the most important task a SQL Server DBA or developer needs to understand. More »


We Loaded 1TB in 30 Minutes with SSIS, and So Can You

In February 2008, Microsoft announced a record-breaking data load using Microsoft® SQL Server® Integration Services (SSIS): 1 TB of data in less than 30 minutes. That data load, using SQL Server Integration Services, was 30% faster than the previous best time using a commercial ETL tool. This paper outlines what it took: the software, hardware, and configuration used. We will describe what we did to achieve that result, and offer suggestions for how to relate these techniques to typical scenarios. Even for customers who don't have needs quite like this benchmark, such efforts can teach a lot about getting optimal performance. More »


From the SQLServerCentral Blogs - Full-Text Search – Thesaurus

I would hope that most of us have used a thesaurus at some point in our careers. These allow us... More »


Editorial - Geeky Oscars

Last fall I heard an interview with one of the people who had worked on Skyfall. I can't remember who it was, but this person mentioned the director had made the film with an eye towards the Best Picture award. It's less than a month away from the Oscar awards, and Skyfall wasn't nominated. I'm not sure it should have been, but I did very much enjoy the film. 

Last year was a good year for geeky films. The top grossing films of 2012 were dominated by those topics that we might see as appealing to the nerds, geeks, and technologists. I greatly enjoyed the top 4 and would recommend to others. However the amount of money a film makes doesn't necessarily mean it's worth watching, and if a film doens't earn much money, that shouldn't mean you want to avoid it.

This Friday, as a break from the software and database discussions we have, I wanted to ask you 

What sci-fi, fantasy, or geek-oriented films do you recommend from 2012?

Did you think John Carter or Total Recall (the remake) were worth watching? Are there other films you'd recommend that might not have been well marketed or widely distributed? With the streaming video services and online rentals, it's easy for many of us to watch films that we might have missed in the theater.

Let us know this week what you recommend and give us an idea for how we might relax over the weekend or throughout the year. At least until the Dr. Who 50th Anniversary special.

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

Everyday Jones

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


Question of the Day

Today's Question:

What is the difference between COALESCE and ISNULL? (choose 2)

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 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Yesterday's Question of the Day

You’re creating a database to contain information for a university web site: news, academic announcements, admissions, events, research, etc. Should you use the relational model or XML?

Answer: Either one is appropriate

Explanation: If your data is highly structured with known schema, the relational model is likely to work best for data storage. SQL Server provides the required functionality and tools you may need. On the other hand, if the structure is semi-structured or unstructured, or unknown, you have to give consideration to modeling such data.

XML is a good choice if you want a platform-independent model in order to ensure portability of the data by using structural and semantic markup. Additionally, it is an appropriate option if some of the following properties are satisfied:

•Your data is sparse or you do not know the structure of the data, or the structure of your data may change significantly in the future. •Your data represents containment hierarchy, instead of references among entities, and may be recursive. •Order is inherent in your data. •You want to query into the data or update parts of it, based on its structure.

Ref: http://msdn.microsoft.com/en-us/library/bb522493(v=SQL.105).aspx

» Discuss this question and answer on the forums

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Featured Script

Table-Column Data Profiler

Profile all of a table's columns using Transact-SQL. 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

merge replication identity check constraint insert fails - Hi, I use merge replication. I tried to insert a couple of records at the publisher. I am 'sa' on...

DBCC CHECKDB FAILED - Hi Experts, Got below messages when ran checkdb Executing the query "DBCC CHECKDB WITH NO_INFOMSGS " failed with the following error: "Process ID...

pass variables between SQL Server tasks in a JOB - I'm using Sql Server 2005. I'm trying to pass a variable between the tasks in a job. NOT SSIS. We...

SQL Server 2005 : Business Intelligence

MDX to calculate measure between two dates - Hi , I have a dimension which has the following data Name StartDate EndDate Duration PreSalesStartDate PostSalesEndDate Sales1 01-Jan-2010 31-Jan-2010 31 01-Dec-2009 02-Mar-2010 Duration refers to...

Exporting a report from Report Builder to CSV - Hi, I have a column that I am exporting from report builder to csv that is 16 digit long (1234567812345678). I...

How do I remove zombie Business Intelligence Development Studio installed by SQL Server Express? - I am at a client where they install SQL Server Express 2008 R2 from a disk that apparently is capable...

ssis space problem - Hi friedns i have small doubt in ssis plese tell me how to solve this issuse i have source table and...

SSAS MDX Query for date comparision data - Hi friends, Can anyone help me out for writing MDX for finding products which are present for current date but not...

renamed ReportServerTEMPDB now errors referencing dbo.executioncache - I know what has caused my issue, it but don't know the solution. I'll explain my issue and hope someone...

SQL Server 2005 : SQL Server 2005 General Discussion

need help regarding Lock Table - What is the procedure to delete some data from Lock Table? We can't use NOLOCK for Update, delete & Insert for that. A...

Simple Delete Query is Taking Huge Time - Hi All, One of simple delete query is taking more than 10hrs to execute. There are more than 12 foreign...

Problem with xp_cmdshell - Hi there, hope in your help. I need to save in my hard disk the txt file generated with this code,...

Auto Save / Auto recover in Management Studio? - I have come in today to find my PC rebooted, I suspect due to windows updates, (it decides to reboot...

SQL Server 2005 : SQL Server 2005 Security

Audit Drop login - hello, what are the different ways to audit if some one drops login, and which would be the best. please reply its...

SQL Server 2005 : SS2K5 Replication

Reinitialize subscription remove index from subscriber? - We reinitialized the subscription for a replication and noticed afterwards that an index the was created on the subscriber database...

SQL Server 2005 : SQL Server 2005 Integration Services

Invoking SSIS Package through a Web App - I have a requirement which needs to create an excel file based on a large customer table. The data will...

Schedule SSIS job in SQL Agent fails - Hi,  I am trying to schedule a SSIS package in SQL agent 2005.  I can run the package manually using...

SQL Server 2005 : T-SQL (SS2K5)

Delete specific node from XML column - TABLE tblProduct ProductId int Productname varchar(20) TABLE tblQuantityP QuantityPId Quantity int Price int ProductId XML DATA IN tblProduct ProductId ProductName 1 Prod1 2 Prod2 3 Prod3 4 Prod4 5 Prod5 6 Prod6 DATA IN tblQuantityP QuantityPId Quantity Price ProductId 1 12 12 <itemids> <itemid>1</itemid> <itemid>2</itemid&g

SQL Server 7,2000 : SQL Server Newbies

Some Help getting started with MDX - I have a measure circ that should roll up along some dimensions but there are three dimensions where I do not...

SQL Server 7,2000 : T-SQL

How can we calculate Age of employee? - Here I have used some queries but i unable to get a exact solution... Please try else Am I Correct? My quires: SELECT...

SQL Server 2008 : SQL Server 2008 - General

Monitoring Tools - Need to install one for my Environment for monitoring 10 instances

SQL definition statement for synonym ? - I have created a synonym as follows CREATE SYNONYM [dbo].[ms4] FOR [local].[Northwind].[dbo].[Products] GO and then i want to retrieve a same SQL definition...

Tables Involved in Stored procedure - A stored Procedure which use to execute in 10 minutes, now it takes 1 hour to execute :w00t: the data inside...

Indexing where table is in join and where clause - I have an example where the table im indexing appears in the join and where clause: [code="sql"] select OL1.a, OL1.b, OL1.c, OL1.d,...

Stored procedure - Trace or track - Hi Team, Am having a stored procedure, it involved conditions, joins. based on conditions and joins it displays some records as output. am...

LOCK TABLE PROBLEM - [b]For SQL Server 2008:[/b] What is the procedure to delete some data from Lock Table? We can't use NOLOCK for Update, delete...

Merge Replication across multiple servers - What SQL version iss required - Hi All - I have a question regarding SQL licensing when using Merge Replication. I understand from my research that I...

Procedure is taking 15 seconds after service restart but from second time procedure takes 1 sec - Hi, I have one store procedure which is taking 15 seconds after restarting the SQL Server Service. But when I try...

Upgrade to SQL 2005 Error - In Inplace upgrade, from 2000 to 2005, got this error Sytem.IO.FilenotfoundException occurred in bpacmd.exe Possible Debuggers How can it be resolved

Linked Servers Syntax - "drop if exists" and "select * into from" - Hi, Can anyone let me know the syntax for "drop if exists" and "select * into from" using Linked Server tables.

select and condition column+column - Hello, is any possible idea for faster performance ? I have two columns date and sequence, so i need to join...

How to Loop through all DBs in an Instance? - Hi, Can anyone provide a method to loop through a DB's in an instance WITHOUT using the proc sp_MSforeachdb I have...

Computed column with/with out PERSISTED - Hi All, While reading about Computed column if found that if we mentioned PERSISTED Key word while creating/altering table then we...

REG: Monitering Tools - Hi Friends, Which is the best monitoring tool for monitoring the Sql Server 2008 R2 If possible can any one send...

Cannot access Analysis Manager in SQL Server 2000 - Hi All, I granted SA to one of the login in SQL Server and now he reports that he is not...

Killed clustered index creation, running out of space - So, I screwed up. Started a clustered index creation on a large table (20 mil rows, 900 columns, about 145...

Multiple CROSS APPLY??? - Hi I was given a T-SQL assigment that at first seemed trivial until I took a closer look to it and...

Subtracting specific dates from today - Hello, I know this isn't too difficult, but I am have a momentary brain lapse. How can I calculate the number...

How to find if a job ran successfully? - I have a sql Job B dependant on Job A. Job B should run only if the job B completed...

How can I find who/what process dropped view/table from my sql server 2008 database ? - Experts, How can I find who/what process dropped view/table from my sql server 2008 database ? Auditing is not implemented. Thanks, Smith

CSV to Excel - How to automate the process of converting csv file to excel file?

Group By Inside CASE Statement - Hi geniuses, It is possible to have a 'GROUP BY' inside a CASE Statement? In order to Sum my values properly? What...

Clock-In/ Clock-Out - I am working on a clock in and clock out system. I have one table with Employee id, punchTime, punchType. test...

PLE (Page life expectancy). - On a production system we have a PLE which is low at some moments. So since yesterday afternoon we have run...

Puzzle, I used SQL to see what happend next. - Some time ago I came across this puzzle. I do not want to spoil the satisfaction of solving this puzzle yourself. So...

Calculating Median, Max, Min, Lower Quartile and Upper Quartile for a Boxchart? - Hi All - My boss asked me to create a boxchart using SSRS for some of our data. I read articles...

Unable to install sql server 2008 Management studio - Dear Sir/Madam, I downloaded Visual Studio 2010 from microsoft so its automatically installed sql server 2008 R2. In Sql server 2008...

When is shrinking a data file acceptable? - Hello All, Long time reader, first time poster. While I agree that shrinking a data file is a bad thing in most...

Can anybody tell a good site for learning Erwin data modeler - Can anybody tell a good site for learning Erwin data modeler and software to down load it

SQL Server 2008 Reporting Services - Is it possible to have SSRS 2008 Reporting Services run with a SQL Server 2005 database back-end?

SQL Server 2008 : T-SQL (SS2K8)

Function to return primary key column value - hi guys i need your help for the below scenario i need to create a function which returns primary key value...

Help with implemting phonetic algorithm - Please consider the following example: [code="sql"]create table #Names (Forename nvarchar(50), Surname nvarchar(50), PhoneticNameKey nvarchar(20)) insert into #Names(Forename, Surname) select 'JOSE', 'ANTINORI' select * from...

where fldValue=123 vs join to table with one row - I have a huge DB and a view that pulls records from multiple tables, some as big as 2M records....

Get distinct value - I have the following tables Product --stored for productid ProductRelation -- storing linked product id's [code="sql"] DECLARE @Product table(ProductID int) DECLARE @ProductRelation TABLE (FirstProductID int,SecondProductID int) INSERT...

Indexed Views - why don't they improve performance?!!!! - I have a query that takes quite a while to run even after optimization. DML statement on the underlying table...

How To Validate in Procedure - I Created a one Procedure ..its given Below... [code="sql"]create procedure [dbo].[USP_Reliance_Master_Upload] as begin declare @dt datetime , @Mnth varchar(10) BEGIN TRY SELECT @dt...

No Wildcards Characters - Hi all, I want to search for "[[% SQL Server %]]" pattern in a string value (Note, ] [ % are not wildcards). But I cannot do...

SQL Index Help.. - Hi guys, I have question for you guys, I have one table ABC (For Example) and Table ABC has few Indexes First...

SQL Server 2008 : Working with Oracle

Need help - MS SQL Trigger for Oracle insert - Hi, is it possible to use a ms sql trigger to insert a row in Oracle Table ? I use this way...

SQL Server 2008 : SQL Server Newbies

Cachestore flush - Hello Masters, Again need your help .. :-) During monitoring I found one of my database in restoring mode from last two days..!!upone...

Forced Parameterization - Hi All Having checked the web and done some testing, I just want to confirm my understanding of SQL's plan cache Am...

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

Translate rows in columns - [font="Courier New"] Hello, I have the following TitleDetailsRequest table: see TitleDetailsRequest.jpg attachment. I need to get an output of the EN_No and FR_No...

SQL Server 2008 : SQL Server 2008 High Availability

To setup an alert for balancing sql cluster nodes in the event of a failover - Here's what I am really looking for... We have a 2 node cluster setup in our environment, and in the recent...

Log shipping - Hi, Can we able to configure for LOG SHIPPING with following requirement, primary server: sql server 2008 enterprice edition secondary server: sql server...

replica of prod server - Hi, I am trying to create a replica of production server, so that we can divert some traffic. I am thinking...

Failover clustering with Database Mirroring - hi all, I'm learning these HA/DR things. [b] "Failover clustering with Database Mirroring"[/b] What does it means? Clustering: Two nodes being synchronized. (Physically & SQL...

Identity columns - Hello all, I have set up one way pull transactional replication with sql 2008 r2 and the subscription was initialized from...

SQL Server 2008 : SQL Server 2008 Administration

SSCM missing services - Hello, I have an instance of SQL Server 2008 R2 running on Windows Server 2003 R2 and the main data disk...

tempdb: only one of several data files is growing on Index-Reorg - Hello there, I'd like to understand a special behaviour. Our tempdb-database is configured having 8 data files. As Microsoft recommends we have...

Finding the size of databse at Server level - Hi all experts, I am a newbie.I am trying to write a query which would give me the server wise level...

view default trace file in SQL profiler - If I open the default trace log in sql server profiler to view. It opens all the rollover files in one...

Adhoc query timing out - Hi We have simple Adhoc queries running from an application. The query runs for hours and gets timed out. At the same time...

Primary key question - I'm planning to move data from one database to another on the same server. I've DDL scripts ready to create...

Issue in migrating reporting services 2005 to sql server 2008 R2 - Hi, I have done the following steps to migrate SQL Server 2005 reporting services to a new SQL Server 2008 R2...

Linked server error in distributed transaction - Can anyone help me with this? I have checked all my settings according to link [url]http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp[/url], and have restarted the MSDTC...

Career : Certification

70-461 Exam Information - Please can anyone give me any further information regarding the 70-461 exam querying a MS SQL 2012 database? e.g. number...

Programming : General

Reverse string without built in functions - Hello all. I am trying to write a function which reverses passed string WITHOUT using any built-in functions So if 'abc' is...

SQLServerCentral.com : Anything that is NOT about SQL!

What is the Best way for getting day wise data in production server in mysql - Can any one help me............. Please tell me what is the best way for getting data in production server. If possible tell...

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

SSRS vs Dundas dashboard - Hi all, What is advantage and disadvantage of using dundas dashboard instead of ssrs? Thanks in advance

problem with setting the subscription for a multivalue parameter as 'All' in a SSRS Report - Hi Experts, I have a multi value parameter named 'Country'. When the report is previewed, the user selects single or multiple...

Error applying filter at tablix level? (Cannot compare data of types System.Decimal and system.double) - When I apply a filter on a tablix i receive the error (the report runs fine without the filter): "an...

SSRS 2008 R2 inserting lines in Table Report - How to insert the Straight lines in tables if we try to insert through Tool Bar option it is inserting...

Database Design : Design Ideas and Questions

database design help please - Hi I am having some trouble on how to normalize some tricky tables. Basically it is a recurrence event table that...

Data Warehousing : Integration Services

Password going Blank: SSIS Connection to Oracle - Hi Everyone, I am having a strange issue working with SSIS OLEDB connection to oracle Database. When I check the oledb...

SSRS Report Server destination - Is it possible to use the Reporting Services Report server as a SSIS destination? In the SSRS Report manager it's possible...

SSIS, creating columns from rows based on id from result set - I have the following and is getting the attached error. I am trying to loop through a table and change...

Data Warehousing : Analysis Services

MDX to calculate measure between two dates - Hi , I have a dimension which has the following data Name StartDate EndDate Duration PreSalesStartDate PostSalesEndDate Sales1 01-Jan-2010 31-Jan-2010 31 01-Dec-2009 02-Mar-2010 Duration refers to...