In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Hosted 5GB free hosted storage with SQL Backup Pro
Download SQL Backup Pro and securely copy your backups to secure hosted storage. You’ll get your first 5GB of storage free. Try it now.
 
SQL Toolbelt Want to work faster with SQL Server?
If you want to work faster try out the SQL Toolbelt. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download the SQL Toolbelt here.
 
SQL in the City SQL in the City is going on another US tour
SQL in the city is coming to the US, sign up to receive free SQL Server training from industry-leading experts and MVPs. Register for an event now: Pasadena -10/9, Atlanta - 10/11, Charlotte - 10/14.

In This Issue

A Beginners Look at Hadoop

Hadoop has been making a lot of noise in the Big Data world. Despite my lack of Linux experience I decided to take the plunge and this is what I found. More »


Forum Etiquette: How to post data/code on a forum to get the best help

Get correct answers to your SQL forum questions faster by making it easier to load your sample data and read your code. More »


Paging Data with TSQL

Greg Larsen shares a number of different TSQL methods to provide paging functionality, for when your application requires that you display only one page of data at a time. More »


From the SQLServerCentral Blogs - Backup a password, change it, then restore it.

A couple of months ago I talked about moving a login from one server to another without the password. The... More »


Editorial - Fear Fear

I'm a conservative DBA. I get nervous when backups aren't running, code isn't in source control, and developers have access to production systems. I've had too many late night pages and weekend phone calls, not to mention many extra hours spent in the office from changes to systems that didn't go well. That latter item leads me to limit the number of changes I make to systems whenever I can, including avoiding applying Cumulative Updates to SQL Server.

When I read an editorial from Glenn Berry, I had to stop and think of whether or not I had a healthy respect for the problems that can occur from change, or if I was being overly conservative (or fearful). Glenn makes a good point that so many people do not upgrade or change their drivers, firmware, or other software. People don't patch their SQL Servers, even with Service Packs. I'm sure some of that is fear, but some of it is neglect as well.

For me the decision usually comes down to examining the reward/risk ratio, trying to understand if improvements are balanced by the risk of downtime. I do value stability above new features, mostly because if problems do occur, I will be the person fixing them. That doesn't mean I avoid all changes. I think Service Packs need to be installed, though not necessarily the first month. I've also come to embrace some of the continuous integration (CI) and continuous deployment (CD) ideas as ways to both reduce a software inventory as well as hold developers to a higher quality standard. However if you want to deploy (and perhaps patch) in a continuous deployment environment, then you should ensure that your CI process performs strong checks and make sure your developers are holding themselves to a high level of quality.

We change the way we work, and the tools we use in technology often. Change is a concept we embrace, and we should since the ways in which our systems work are regularly changing. Bugs are patched, new techniques and tools are developed that should make us more efficient and productive. Those don't always work, and we should be wary, but we should also not fear change. We should evaluate each new possibility with the attitude that our decision to move forward "depends." It depends on the ease with which we can integrate something or apply a change, and the ease with which we can roll back our changes if they do not perform as expected. It also takes practice to ensure that all those things are easy.

Steve Jones

» 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


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. 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 www.everydayjones.com.

You can also follow Steve Jones on Twitter:


Question of the Day

Today's Question:

What is the output of the statements below?
DECLARE @Var VARCHAR = NULL
IF @Var = NULL
 PRINT 'Statement1 : Equal Null'
ELSE 
 PRINT 'Statement1 : Not null'

IF @Var is NULL
 PRINT 'Statement2 : Equal Null'
ELSE 
 PRINT 'Statement2 : Not null'

SET ANSI_NULLS OFF

IF @Var = NULL
 PRINT 'ANSI_NULLS OFF Statement1 : Equal Null'
ELSE 
 PRINT 'ANSI_NULLS OFF Statement1 : Not null'

IF @Var is NULL
 PRINT 'ANSI_NULLS OFF Statement2 : Equal Null'
ELSE 
 PRINT 'ANSI_NULLS OFF Statement2 : Not null'

SET ANSI_NULLS ON

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

This question is worth 1 point in this category: NULL. 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

What will be the output of below query when it is executed? (In the answer options, the rows are comma separated)

SELECT TOP(6) WITH TIES COL 
FROM 
(
SELECT 1 COL
UNION ALL
SELECT 2 
UNION ALL
SELECT 3 
UNION ALL
SELECT 4 
UNION ALL
SELECT 5 
UNION ALL
SELECT 4 
UNION ALL
SELECT 3 
UNION ALL
SELECT 4 
) A
 ORDER BY COL

Answer: 1,2,3,3,4,4,4

Explanation: Using TOP with "WITH TIES" give all matching values with the last TOP (n) rows in ORDER BY columns. In simple way, if you will specify top 3 then it will give result 1,2,3,3 as there are two same value in that column.

For top 5, it will give the result as 1,2,3,3,4,4,4 because top 5th value is 4. It will find same value in the column used in ORDER BY until the last row of the table.

That's why top 6 returned 1,2,3,3,4,4,4 as top 6th is 4 so it will look for value 4 in entire COL column values. If matches are found, then it will include all those as well.

Refs: http://msdn.microsoft.com/en-IN/library/ms189463(v=sql.90).aspx
http://blog.sqlauthority.com/2009/12/23/sql-server-order-by-clause-and-top-with-ties/

» Discuss this question and answer on the forums

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.


Featured Script

Search Anything Anywhere

Find a specified string anywhere in the database. 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

DBCC CHECKDB with allocation error - Hi, After server shutdown in the midnight due to power failure, our DB backup maintenance plan stopped running. Executing DBCC CHECKDB...

Will the speed of index rebuild be affected by log or DB backups ? - Experts, Will the speed of index rebuild process be affected by log or DB backups ? Thanks, Smith.

MSDB Huge, and Rapid Growth !! - For some reason mu MSDB database has grown huge in just a few days. June 1 backup is 3.5 gig June 2...

Separate one column in many columns - I have a table with the next structure: CREATE TABLE students ( ID_Student INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Name nchar(30), rate nchar(5) ...

Unable to connect to database after domain migration - Hi We just migrated our windows 2003 server to a new domain. We created a new service account and set the...

Split a column - Hi: I would like someone can help. I heve a column with many rows like: NAME Jonh*Smith*Alu Alex*Campos*kery Chipper*Jon*Ali Larry*Walker*kin AND...

SQL Server 2005 : Business Intelligence

Update column and pass new value to other dataflow - Hi All, I created a SSIS package that check first whether the ID in the file being imported matches to an...

SQL Server 2005 : SQL Server 2005 General Discussion

DTS error - I have several jobs on server that uses dts for creating excel files and populating data from sql server table...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS variables not getting set when executed in a SQL job - I have a package that loops through a directory and stores all the file names in that directory to a...

how to use parameters in SSIS update query having CASE[ Giving Error] - My below query is giving error while writing in query editor [ SSIS]. [quote] declare @janflag as varchar(56) set @janflag ='y' declare...

SSIS 2008 - Failed to lock variable Error (Oh, I fixed it) - I have a package in SSIS that I just created. It contains 2 ForEach loop containers. Within each container are...

Excel Datasource brings in a couple of numeric fields as nulls? -   I have the Excel Connection Manager and Source to read the contents from an Excel file.  For some reason couple...

SQL Server 2005 : T-SQL (SS2K5)

So I got this .dbf file... - And the date column is showing up like this in Excel: BDATE 380629 300517 670520 660402 470228 860421 550102 410322 491101 430612 Which, next to the Age column, makes sense that it's...

remove last character and reformat date - Hi, I have a field (called 'perControl') that contains a date value, plus one character. But the value is formatted like...

VB.NET TreeView using Stored Procedure - There are 5 tables we have which has a parent child relation we have following tables. Order (Fields Order#, RefType, RefNum,...

SQL Server 2008 : SQL Server 2008 - General

How To Prevent New Line/Carriage Return In Query Results Within SSMS - I realize there is probably a term or common phrase to describe this ( something better then New LIne / Carriage Return)...

Allowing users to directly query data mart - I am looking for resources to help evaluate options for allowing users to query our data mart directly. (T-SQL). These...

How To Prevent New Line/Carriage Return In Query Results Within SSMS - I realize there is porbably a term or common phrase to describe this ( something better then New LIne / Carriage Return)...

Taks SSIS-package is not executed at all - Hi, there is a for-each-loop-container within a simple script task to set a variable and two following data-flow-tasks. Between Script- and data-flow-tasks...

Creatin of role & assign users - One Employee have been asked to Create a User defined Role named (ReadOnlyXYZ) then create individual 90 login account :w00t:with read...

How to get a list of replicated articles ? - I need to programmatically to get a list of all replicated articles for a given database. I tried select * from...

Application not Working - Here we have an application which uses a Stored procedure. The user have created a SP but the application fails to...

differences - what are the differences b\w 32 bit vs 64 bit in sql ?

What are the options for splitting/reducing large mdf files? - I have a database with 2 mdf files. One file is 750gb and the other file is 112gb. There are...

SSIS Package execution progress - HI, We recently upgraded our servers to 2012 and when running SSIS packages from cmd it's giving execution progress of each...

Who Dropped Database - Hi Team, How can i know who dropped database in sql server... Plz bit urgent.

Database Virtualisation Tool - Hi, I would like to know if anyone has come across or used Delphix virtualised tool? Thanks

SQL Server 2000 to SQL Server 2008 Migration and Performance - Hi, am new to sql server dba and recently my client has moved his db's from SQL 2000 to SQL...

Profiler Events - Hi All, what is the difference between show plan xml,show plan xml for query compile,show plan xml statistics profile in...

Find the error column and the row - Hi All, I am facing a problem with finding the exact error row and column where the error occured. Scenario : 1. TableA...

Database mail sending fails with SP_Send_DBmail. - Hi, I am stuck in below problem. I have two servers; ServerA and ServerB. I have linked serverA to serverB by sp_addlinkedserver @server...

What is the uses of Environment variable explain give me some example. - Hi , i am new to ssis ,but in my package used Environment variable .Plaese explain give me with some example. Thanks, bal85

configuring alerts - i would like to configure alerts in all the sql servers to the below 1. disk space on the server...

SID and PID - Hi can any one explain what these SID and PID . what is the use of them..

HOW TO EXECUTE 2-3 SQL STATMENTS ONE AFTER ANOTHER BASED ON THE RESULT OF PREVIOUS QUERY? - Hi, Ho I can run 2-3 sql statments in one go, which are based on above's query value. for an example. first statment...

How to find SQL Server account name ? - Hi, I need to find account name that SQL Server instance is installed. Is it possible ? Thanks

Bulk insert Error - Hello All, Currently trying to insert a data from a file placed in a remote server and using the BULK INSERT...

DROP PK on highly called big table - HI I have a table with 80 million records with PK on an int column. A service selects data from this...

SSIS package execution remote vs / local - Hi all. Hope I'm posting in the right forum. We currently schedule SSIS packages using the Windows task scheduler, but we...

SSRS Plug-ins - Hi, I have users who want to graphically create their own SQL Reports (in R2 and 2012) . We have setup...

SSRS - reports rendering incorrectly - Hi all I am currently having this problem on SSRS rending incorrectly. The problem is basicall that reports do not display correctly...

How to calculate space needed to modify a column in a table? - I have a situation that's driving me INSANE... The vendors of our financial processing system came to us asking that we...

List of queries using MAXDOP - Gurus, currently, in our environment we configured our sql servers for MAXDOP of 4. here is our config: 4 CPUS - each...

Agent Job Links to Another SQL Server and Fails due to Service Account Privs - To my friends in the community, here is a stumper that I can't seem to find sufficient information on despite...

Help with next date in SQL query - What I need is to be able to find out when a customer next called in from the date I...

Best way to find certain records. - I have to find certain data from a table based on date range & and activity. So, if a records is 60...

Go from SQL 2008 R2 standard edition to Developer? - Is it possible to 'downgrade' SQL 2008 R2 from standard edition to Developer edition? I'm working on developing a long-term...

Debugging SQL server Stored Procedures - HI Everyone, Any one out there, Please advise me the alternate permissions other than the SYSADMIN to grant the user to debug...

SQL Server 2008 : T-SQL (SS2K8)

Counting Days with Reset - I have been struggling with this all morning, and I think I am missing something obvious. What I need to...

Selecting XML Data - Hi I'm trying to execute a script in SSMS using SQL Server 2008 R2, but it's returning the following error message:"Msg...

MY query is procedure is not working? - Hai Friends, i made on application my input fields are name,class. where already ve the ID,and name means go to update...

SQL Permission Script needed - I am looking for a simple sql permission script. It needs to show me username, database role membership eg. db_datareader, db_datawriter...

column with foreign keys - I have a table with a column id and a column with a list of foreign ids. ------------------------------------ | ID | PRICE | LIST_OF_FOREIGN_IDS...

Break a String apart - Hello Everyone I hope that you are having a very nice monday. I have found a column that is storing incorrect data....

selecting Distinct value in a column based on values another column. - Hi Experts, I have 2 columns as below -------------------------------- taxid tax_Combination -------------------------------- Bed+Vat ! Vat Bed+Vat ! Cess Bed+Vat ! BED VAT ! Vat CST ! CST from a query i will get the...

creating procedure for Insertion? - Hai Friends , I m creating one web application in that input fileds are departuredate,from_place,To_place,travel mode. My condition for insertion when i...

SQL Server 2008 : SQL Server Newbies

defferred name resolution - Dear All I am using sql 2008 . While compiling the procedures it does not give error even if table not present. I...

Using AND - Hi, I am confused with a query that: SELECT STNNo FROM table1 where VoucherBookletNo = 'VVB0000021' AND VoucherBookletNo ='VVB0000025' When I run this query...

INSERTING RECORDS WITH TRIGGER - INSERT INTO ACC_JVDTL_TRN ( FINYRTRN_ID, VRDT, LEDGER_ID, AMOUNT) VALUES ((select i.FINYRTRN_ID from inserted i), (select i.BILLDATE from inserted i), (select i.BILL_LEDG_ID...

Calculating Sales History for Months/Years - I have a table - TrnDate, TrnYear, TrnMonth, StockCode, InvoiceQty The TrnYear and TrnMonth are the Financial Periods and dont always tie...

msbi - currently i am undertaking msbi course.. will it be a added value to my resume in which direction i should look...

How have Trigger's example after insert send to mail on outlook?. - I create trigger after insert into table i want send mail on outlook and example trigger please. thank you.

SQL Server 2008 : SQL Server 2008 High Availability

Logshipping Configuration error:3201 - [b]Hi am configuring Logshipping on SQL Server 2008 R2 servers, my primary and secondary both servers are same editions and...

Installing Windows Service Pack on Cluster - suggested way? - Can someone direct me to a Microsoft article or possibly a SQL Server Central article showing the suggested process to...

Need to install ssqlserver service pack sp4 in a cluster server - Need to install sqlserver service pack sp4 in a cluster server 1.what are the pre-requesties i have to follow 2.cluster...

SQL Server 2008 : SQL Server 2008 Administration

Batch Request/Sec Bottleneck? How do you know? - Hello all, I have been reading about batch requests/sec as a stat for measuring performance but what I fail to...

SQL Error Log AWE not enabled - Spotted this in SQL ERROR log "SQL Server is not configured to use all of the available system memory. To enable...

Previously registered servers awol - Hi all, Wondered if anyone could assist? I've logged on to one of my SQL Server 2008 R2 instances and have received...

Production Database in Suspect Mode... - Production Database in Suspect Mode... Luckily we switched the nodes and working on other but trying to find the root cause...

Questions on Index Rebuilding (not reorganize) - Hi Guys, Need some information on Index Rebuilding. (sort_in_tempdb = off) When performing index rebulding (for indexes > 30% fragmented), will we expect...

Transaction Log File "Does not Exist" - Help, please - I'm having problems with modify the settings on a secondary transaction log file in SSMS, so I thought I'd try...

Career : Certification

70-448! - hello. I am preparing my exam 70-448 and want to do more test. What exams you recommend me? I saw SelfExamEngine and...

SQLServerCentral.com : Anything that is NOT about SQL!

Can anyone recommend good course/event management software - It has to handle secure, online payments and preferably not be SaaS. Ideally with a SQL Server backend? We have...

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

Compare 2 tables... How to do that??? - Hi SQL GURU's, Im trying to compare 2 table but i dont want that Table2 use a text .noc in the...

An error has occurred during processing of the CustomReportItem. Could not load file or assembly 'Microsoft.ReportingServices.Designer, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An attempt was made to load a program with an incorrect format. - Need immediate help on the below issue. I'm getting above error when I run an SSRS report. Environment Details: 1. SSRS 2012...

Cannot create Linked Report in Report Manager w/Query Based parameters - I am having an issue where we can not create a Linked Report from within Report Manager when the report...

Reporting Services : Reporting Services 2005 Development

Evaluating bit fields in SSRS - Can SSRS evaluate a bit field without having to specify the comparison? Example: =IIF( Fields!IsState.Value and Fields!RegionID.Value <>"12","Yes","No") In this example IsState...

Database Design : Hardware

Hidden SAN fragmentation - Hey folks, a quickie question about the drive space assignment mechanics. We'll simplify things. Working on a single drive in a...

Data Warehousing : Integration Services

Help needed for creating the star schema - Hi, We are planning to data warehouse the 2 different projects , to implement this we have to create the star...

finding and replacing ID's and Updating dataflow with new ID --HELP!! - Hi All, I created a SSIS package that check first whether the ID in the file being imported matches to an...

SSIS 2008 R2 - FTP - fails when run from a job, but ok when manually - I am trying to stay away from another batch file, is possible. I have an SSIS package that pulls data...

help writing SUBSTRING() expression please - I have strings like this: MFMGOOGLE, inside my [Business Unit] column, from which I need to strip the "MFM". Yet...

SSIS Package Won't Stop Running and Locking Files - We are running SQL Server 2008 R2 on a 64bit machine, I have several SSIS package that are run via...

SSIS - specified path, file name, or both are too long - Hi All, I have built a package which: 1. searches for flat files through a Folder structure on the test server 2....

Data Warehousing : Strategies and Ideas

Retrieve more than 1 million rows from Cube - Hello All, Am new to ssas and been trying to implement it for our current system Here is what i have built Fact...

Data Warehousing : Analysis Services

Trend indictor in SSAS 2008 showing wrong - Hi, I am defining a KPI. For the trend indicator it will show a green arrow when I have a lower...

Data Warehousing : Data Transformation Services (DTS)

Change Tracking - Hi, We have a need to load some OLTP data into DW based on last time the data got updated/inserted....

Microsoft Access : Microsoft Access

Wny "JOIN expression not supported"? - Hi everybody, I thought I had a well laid out query but I keep getting the "JOIN expression not supported" error....

Article Discussions by Author : Discuss content posted by Deepthi Viswanathan Nair

How can I add custome tag(Inserted, Updated, deleted) into column of status based on Insert, Update and Delete operation. - Hi , when I run this , I want to add one tag out of three based on the operation it does...