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

Competition or Cooperation

I once worked in a company with a stack ranking system. I was a manager, and hated the idea of ranking the people reporting to me in this system. I had a good staff, managing all three major RDBMS platforms (Oracle, DB2, SQL Server), and they performed their jobs well. At review time, when I was told I had to have some 2s (on a scale of 1-5) in addition to 3s and 4s, I was distressed. Having worked with and managed hundreds of people in my career, none of my staff was below expectations (a "2"). However I had to rank at least one person at that level.

Microsoft has had this "stack ranking system" for years, and there has been plenty of speculation that it hasn't encouraged the really talented people to work together, but instead it pushed employees apart. Apparently they've decided to move away from this system according to numerous reports. I have heard that the most talented people tried to ensure they weren't working on the same team with other talented people, just to ensure they would continue to be highly ranked.

I believe in teams. I believe in getting people to work together, and help each other. I know that successful software projects, those that require more than one developer, are never successes or failures because of one person. I know people contribute more or less than others, but the strength and value of those contributions are not strictly individual talent or effort. We need talented coders to solve tough problems, and we need average programmers to tackle mundane tasks. Both are necessary (along with many other roles) to ensure success of a project. 

Cooperation, assistance, teaching, learning, working together as a team, with each person helping the others to succeed is how I like to work. We don't need winners and losers; we need success from everyone.

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 ( 21.5MB) feed

MP4 iPod Video ( 21.5MB) feed

MP3 Audio ( 5.1MB) feed

Feeds are available at iTunes and Mevio

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

ADVERTISEMENT
SQL Monitor

Optimize SQL Server performance

“With SQL Monitor, we can be proactive in our optimization process, instead of waiting until a customer reports a problem,” John Trumbul, Sr. Software Engineer. Optimize your servers with a free trial.

SQL Compare

Need to compare and sync database schemas?

Let SQL Compare do the hard work. ”With the productivity I'll get out of this tool, it's like buying time.” Robert Sondles. Download a free trial.

SQL DBA Bundle

The seven tools in the SQL DBA Bundle support your core SQL Server database administration tasks.

Make backups a breeze! Enjoy trouble-free troubleshooting! Make the most of monitoring! Download a free trial now.

Featured Contents

 

Stairway to PowerPivot and DAX - Level 5: The DAX ISBLANK() Function

Bill Pearson from SQLServerCentral.com

Business Intelligence architect, Analysis Services Maestro, and author Bill Pearson exposes the DAX ISBLANK() function, and then provides some hands-on exposure to its use, particularly in combination with other DAX functions, in managing empty values underlying our PowerPivot model designs. More »


 

Free eBook: Defensive Database Programming

Press Release from Red-Gate

Resilient T-SQL code is code that is designed to last, and to be safely reused by others. The goal of defensive database programming, the goal of this book, is to help you to produce resilient T-SQL code that robustly and gracefully handles cases of unintended use, and is resilient to common changes to the database environment. More »


 

Getting Started with Microsoft Power Query for Excel

Additional Articles from Database Journal

Power Query, provided as part of Microsoft Power BI self-service solution, is an Excel add-in that can be used for data discovery, reshaping the data and combining data coming from different sources. Power Query is one of Excel add-ins. Read on to learn how to install and get started using it. More »


 

From the SQLServerCentral Blogs - SSRS – Self Referencing Reports or Drill through to same report

MikeDavis from SQLServerCentral Blogs

When looking at a report and wanting to drill through to get more detail, we often create two reports for... More »

Question of the Day

Today's Question (by Igor Micev):

True or False: When you edit a DQS Cleansing component in SSIS 2012 package you must have a DQS server installed and a Knowledge Base?

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

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

Delivering Business Intelligence with Microsoft SQL Server 2012

Equip your organization for informed, timely decision making using the expert tips and best practices in this practical guide. Delivering Business Intelligence with Microsoft SQL Server 2012, Third Edition explains how to effectively develop, customize, and distribute meaningful information to users enterprise-wide. Learn how to build data marts and create BI Semantic Models, work with the MDX and DAX languages, and share insights using Microsoft client tools. Data mining and forecasting are also covered in this comprehensive resource.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday'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'

Answer: PRIMARY, QOTD_index, QOTD_index

Explanation:

Directly from BOL:

Because the leaf level of a clustered index and the data pages are the same by definition, creating a clustered index and using the ON partition_scheme_name or ON filegroup_name clause effectively moves a table from the filegroup on which the table was created to the new partition scheme or filegroup. Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index.

http://technet.microsoft.com/en-us/library/ms188783.aspx


» Discuss this question and answer on the forums

Featured Script

Capture and Alert for Blocked SPIDS with SP_WHO2

Joshua A. Walker from SQLServerCentral.com

This script basically keeps an eye on SP_WHO2 for any process that is blocking another process. If it finds one it gathers all the needed information and creates an email that is sent to the passed address.

There are comments where you need to fill in your company specific information. Databases, email addresses, email profile name...etc...

This script includes a table create script for storing the history of the blocking.

It includes a stored procedure for capturing the data and calling a send mail script.

It includes the send mail script if you don't already have send mail script.

It includes the script to create the job that will watch for blocks every 2 minutes.

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

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


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

odd situation - How can u have the same tablename in sysobjects w/ different id's. It's making one of my processes fail. The...


SQL Server 2012 : SQL 2012 - General

DNS Aliasing, Reporting Services, and Kerberos - We're setting up DNS Aliasing on one of our SQL Servers, but I'm unable to get Kerberos to work from...

Why sql server didn't find my temp table - Hello I define a temp table to fill with execution of stored procedure, but I have an error that [b]Invalid object name...

Mixing SQL Server 2012 \ 2008 Instances on a Failover Cluster - Hi, First off, wasn't sure which forum to post this into as it's kind of about 2012 AND 2008.. Hopefully...

Twice join same table with left join issue - Hi All , I have table with data like below [b]table name : #tmp_h [/b] [code="sql"] Name marks month subject Kumar 80 1 maths Guna 70 1 science Kumar 50 2 physics Guna 40 2 science [b]Query[/b] select t1.name,t1.marks-t2.marks as Marks,CONVERT(VARCHAR(19), t1.month)+'~'+CONVERT(VARCHAR(19), t2.month) as Month, t1.subject from #tmp_h t1...

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

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

Filter using dates - Hi all, I need to filter a query with the WHERE statement. The column (called CreatedOn) I need to filter contain...


SQL Server 2012 : SQL Server 2012 - T-SQL

Query approach - I can figure a number of Big Hammer methods of doing this job, but I"m trying to improve myself and...

sys.default_constraints bug - Hi all i've executed the following [code="sql"] CREATE DATABASE practise USE practise CREATE TABLE dbo.SUPPLY1 ( supplyID INT CONSTRAINT SUPPLY1_pk PRIMARY KEY CONSTRAINT SUPPLY1_chk CHECK (supplyID...

Normalizing using Cross Apply... with a twist - I was reading Kenneth Fisher's article on using Cross Apply to normalize an unnormalized table. (I had to do this...

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

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

Could this be done better? - Howdy, Late last night I got a request for some data. The format was the biggest challenge, since the definitions of...

Replacement for a whole mess of left joins? - Hi, A bit of a general question: I have a query with 28 LEFT JOINS. 20 of them join one table to...


SQL Server 2008 : SQL Server 2008 - General

Execute SQL Proc - Hi Guys, I need to execute sql server proc from MS Access or Excel... the proc has couple of parameters, but...

MSDB Migration - Hi there, I am currently in the process of migrating a SQL 2008 R2 Cluster onto new hardware. I have set...

sql restore error - Dear all, I'm trying to restore an SQL Server database from one server to another but I get the following error: The...

Who is in the DBO group for all dbs. - Hi I need a script as part of a security audit. Any pointers would help. Even a table name where this stored...

DBCC Maintenance task failure - I am using SQL 2008 R2 10.5.2500 I have a Maintenance plan, with 2 sub-plans. 1. CHECKDB for System Databases and...

Datamart dimension historical column ok? - I am working on my first data mart. It is going to be populated with natural disaster data for one...

How to display date and time in string from datetime - Hi, My requirement is, I want to capture exact date and time, I am trying using, Select Convert(varchar(10),GetDate(),112) output :- 20131203 Above statement gives me...

Finding missing numbers - I have a large table of order numbers that should be consecutive. What I'd like to do is find a...

Status Bar shows "Executing Query" but the result is viewed through other query windo - I have peculiar question... The query windows shows "Executing Query..." but when the result is viewed though another window, I found...

Try to add a column - Hello SQL Guru's Im trying to add a column in a table but i get the following error; Msg 515, Level 16,...

sys.dm_os_process_memory - doubts - Dear all, The column physical_memory_in_use_kb in sys.dm_os_process_memory represents process working set of SQL Server as per BOL. Does that include MTL...

Same table twice left join issue - Hi All , I have table with data like below [code="sql"] Name marks month subject Kumar 80 1 maths Guna 70 1 science Kumar 50 2 physics Guna 40 2 science [b]Query[/b] select t1.name,t1.marks-t2.marks as Marks,CONVERT(VARCHAR(19), t1.month)+'~'+CONVERT(VARCHAR(19), t2.month) as Month, t1.subject from #tmp_h t1 left join...

SSIS over internet secure? - We have a 3rd party company that needs data from one of our databases. One of the options they gave...

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

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

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

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

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

Parsing EDI 834 flat file with SSIS, I need to be able to use multiple delimiters - Hello All, I am working at a small health plan and I am trying to parse a 834 flat file data...

install SQL Server 2008 Client tools - Question: We are rolling out SQL Server 2008 R2 and I'm wondering if there is a way to install just...


SQL Server 2008 : T-SQL (SS2K8)

Minimally logged INSERT with TABLOCK - I know that if I use the syntax [code="sql"]INSERT INTO <DestinationTable> (<Columns>) WITH (TABLOCK) SELECT <Columns> FROM <SomeStatement> [/code] I will have a...

file rename with store procedure - Hi all, I've files with an unix timestamp as name. For instance filename: 1081277409 I've written a function that based on the...

Check Foreign Key values - HI all, I have a scenario... For Example, I have a ITEM table with structure - CREATE TABLE ITEM ( ItemID INT NOT NULL...

Storing Text Messages in a table - Good Day, we are running SQL Server 2008r2 in our Production environment and 1 of the requirements for the table is...

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

BCP to pipe delimited format - Server 2008:ABC Database:DEF Stored proc:up_CheckProc";1 'ZZZ', 1, 0, 1, 1, '2020-04-01 00:00:00.000', '2040-09-30 23:59:29.000', '', '', '', '', '', '', '', '', '', '', ' Output should be to E drive:E:\output.txt in pipe...


SQL Server 2008 : SQL Server Newbies

SQL Select statement - [code="sql"] SELECT C.containerCode + ':' + C.containerName as display, C.containerCode AS code FROM Containers C INNER JOIN ContainersTypes CT ON CT.containerTypeSqlId = C.containerTypeSqlId AND CT.containerTypeIncId = C.containerTypeIncId WHERE C.isDeleted = 0 ORDER BY display [/code] Hi I...

Career path.. - Hi I have been working as jr.DBA in cmm level 3 company in india .But I am much more into...

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


SQL Server 2008 : Security (SS2K8)

Permission on a trigger - Hi All, How can I grant a Update Permission on a "Delete" trigger on a table? Thank you.

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 Administration

Rebuild Index Maintenance Plan with Default Amount of free space - I Rebuilt Indexes on a Database with the Maintenance Plan and specified free up with the default amount of free...

Migration: detach & copy vrs backup & restore - I'm planning a migration of a third party app's databases to another server. The vendor is recommending the following steps:...

Unable to Start SQL Server Agent Service with sa privileges - I am having sysadmin privileges on a SQL Server 2008R2 Enterprise Instance. But not sure why I am unable to...

Unable to configure data collector set - Hi friends, Am unable to configure data collector set. When I start collect & upload now, I get the below error. How...

Database Growth - Hi, If a database is used only to read from, will its data files still grow? Only select queries are executed...

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

server automatic restart - We have a windows 2008 server sp2 that hosts a SQl server 2008. Recently the server has an automatically restart at...

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 2008 : SQL Server 2008 Performance Tuning

CTE WITH CONDITION TAKING TOO LONG............ - Hi Polks, ;With Cte as ( --- some joining query here with 3 tables --first table has 30 crores records,second one has 6 crores ,third...

Performance issues with SP - Hi, We have a stored procedure which updates table A every 5 mins whole day(9AM to 7PM).Table A has only one...

Recursive CTE problem - Hi. I have a problem with this one ITVF. It was working fine, but I had to change it so that...

Index Tuning never finishes - I have been given several large databases to tune. They have never had the statistics updated and I cannot tell...


SQL Server 2005 : Administering

FAIL_VIRTUAL_RESERVE 65536 - both -g & -T845, is it OK? - Hello, Can I add both -T845 and -g startup parameters to my SQL Server? Will they cause any startup or run time...

select query - i need a select query for below. there is 3 date colums in a table. how to find out the greatest value...


SQL Server 2005 : SQL Server 2005 General Discussion

Is this good practice for using a view? - We have a large lookup table with 200k records. This table is used frequently in a high volume database. We...

Datetime as foreign key - Hi i've got a few tables with start/end date fields in. I also have a calendar table. should i have a...


SQL Server 2005 : SQL Server 2005 Integration Services

Writing single excel file dynamically to sql server table using script task - I am trying this on SSMS 2008 . Since there was no group as SQL Server 2008 Integration Services , i am...

SSIS Ignore Dupes - I apologize in advance as I'm sure this has been answered before. I did some searching and couldn't quite get...


SQL Server 2005 : T-SQL (SS2K5)

create date list - Hi i have a table with accountid, stockid, startdate, enddate. i need to create a table which is a list of dates...


Reporting Services : Reporting Services

Remove and Display data in Column in Report Server - Goal: To remove data in the column "Last Run", "Modified Date" and "Modified By" in Report server. The second goal is to...

Char type - I need to display a bunch of data for a single category. Category - IT and Hardware Configuration_Items - Servernames approx 6000 I do not...

Display the Latest Date and Time - Goal: Display the value of the latest date and time of the cube's process in SSRS Problem: Based on the code below, I...

Create Customized Functionality in SSRS:s Report Server - Goal: Create an additional functionality in report server. It is a button or an address link and when you press it...

Procedure with default parameters - Guys, Unusually I'm using a production procedure in our of my reports, so we return *identical* data with the logic centralised....

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

Creating % based on total of rows - Hi I have a report that shows the time it took to do jobs as well as the time it was...


Programming : General

SQL Syntax Question - To all: I am working on a SQL query as follows and am hoping for help troubleshooting and answering questions: DECLARE @Param...


Data Warehousing : Integration Services

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


Data Warehousing : Analysis Services

Unique Identifier Format... - There are two Cube Databases in the same server with identical schema. In than we have created a ProjectID dimension on...

MDX to exclude and filter sets - For several days, I've been trying to write mdx select correctly, but the result I get every time is either...


Database Design : Design Ideas and Questions

Database Role Design/Standard - First, I apologize if this topic wasn't the right place for this post. I am looking for a good standard for...


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

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


SQLServerCentral.com : SQLServerCentral.com Website Issues

Can't subscribe to 2014 forums RSS in Feedly - It could definitely be a Feedly issue, but I can't subscribe to the 2014 forums. Feedly says "Access Denied". All the...


SQL Server 7,2000 : Working with Oracle

How to find index fragmentation in Oracle - Morning All I need your help on how to find index fragmentation in Oracle 11g as i'm new in Oracle, in...


SQL Server 7,2000 : T-SQL

Help getting a count by consecutive groups - I am hoping someone can help me figure out a solution to a TSQL problem I have. I have data...

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