In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Source Control Connect your existing source control system to SQL Server
SSMS plug-in connects SVN, TFS, Git, Hg and all others to SQL Server. Get started with the 28-day free trial.
 
SQL Backup Pro New! SQL Backup Pro 7.1 - faster, smaller, verified backups
Try new 7.1 for easy automation of DBCC CHECKDB, so you can be sure your backups aren't corrupt. Get faster, smaller, fully tested backups today. Download now.
 
SQL Connect Develop seamlessly between Management Studio and Visual Studio
SQL Connect is a Visual Studio add-in that makes it easy to keep your database and Visual Studio project in sync. Find out more.

In This Issue

Display data by fiscal year instead of calendar year

This article provides a simple solution to display any data by fiscal year instead of calendar year. More »


SQL Saturday #162 - Cambridge, UK

Come to Cambridge in the UK for a free day of training on SQL Server. Steve won't be there, but plenty of other Red Gate'ers will be. More »


Optimizing tempdb configuration with SQL Server 2012 Extended Events

One of the most obvious bottlenecks in the performance of tempdb is caused by PAGELATCH, in-memory latch contention on the allocation bitmap of each data file used. We can use one of the rules-of-thumb to choose what should be roughly the best number of files, but how then do you check to see whether you've got it right for your data and workload? More »


Utilize SQL Server Storage Effectively

The reality is raw storage is cheap, but SANs, software and management of the storage is not. Fortunately, if you plan your database and data management correctly you can build and manage a cost effective yet high performing SQL Server solution. This session will introduce some best practices and options focused on helping you manage your SQL Server storage in an easier way using the native SQL Server toolset. This session will dive into some of the latest trends, database design implications, data archiving and alternatives such as SQL Server compression, BLOB data storage, filtered indexes and more. We’re also going to show you one Red Gate tool that can make you even more efficient in managing your SQL Server storage. Wed, Aug 15, 2012. 11am EDT. More »


From the SQLServerCentral Blogs - Creating a DDL Trigger

One of the most amazing features to an old SQL Server 4.2 guy was the addition of DDL triggers to... More »


Editorial - Stupid Interviewer Tricks

Today's editorial was originally published on Feb 7, 2008. It is being re-published as Steve is on vacation. 

Is this a trick or not? An interviewer telling someone they're wrong when they're not.

This is a blog post from Michael Coles, and from his point of view, it seems that the interviewer is probably not the brightest SQL Server bulb in the bunch, and that's how I initially took the post. But could this be a technique to see how you respond? Not that it's a good technique, but could it be contrived?

Suppose that you have some developers that are sharp, but would easily cover their ignorance with loud bluster. I've certainly met a few in my time (and DBAs as well). How would you hire a DBA to deal with them and ensure he or she didn't get bullied into doing something they felt wasn't the best solution for that application?

You can easily ask someone how they deal with adversity, but it's an abstract question and you get abstract responses. You might get an example from their past, but it's still somewhat abstract because you never know the degree to which it was an adverse situation. So what's the best solution?

Maybe it's the Monte Python solution, where you simulate the actual events, however bizarre they might be. Maybe you would choose to act like one of your belligerent people. Or even include the belligerent person and let them run the interview.

It might be one way to see how the person would actually react. It might be somewhat unfair as people in an interview are usually more nervous and less confrontational than they might otherwise be. However it's definitely one way to see how someone might deal with the situation. If they wouldn't want to work in an environment with regular arguments, then they might not be the best fit.

Personally I wouldn't be offended if someone told me I was wrong. I'd give my reasons for my answer and debate the question. I'm not afraid of arguments and not usually intimidated, but I want to be sure that I have the opportunity to argue my side. If I can't do that, then I don't want to work for you.

Steve Jones


The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). 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.

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.

» 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


Question of the Day

Today's Question:

How many rows should the four execute Queries return?

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 Query Performance Tuning

SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today.

Get your copy from Amazon today.


Yesterday's Question of the Day

In SQL Server 2012 RTM, which of the following are valid index types?

Note that some index types may require additional attributes to be specified; this does not invalidate them for the context of this question. Only index types that include illegal attribute combinations or non existing attributes should be discarded. Also note that this question is not about the exact spelling of the various options.

This is a question with multiple correct answers. Please select 3.

Answer:

  • unique, nonclustered
  • not unique, clustered
  • secondary xml for value

Explanation: Traditional (rowstore) indexes can be either clustered or nonclustered (using the keyword CLUSTERED or NONCLUSTERED), and they can be either unique or not unique (using the optional keyword UNIQUE). All four combinations of these attributes are allowed.

Columnstore indexes (new in SQL Server 2012) can only be nonclustered; this is a mandatory keyword in the syntax for creating a columnstore index. This may change in a future version, but for SQL Server 2012 RTM, this limitation applies.

XML indexes can be either primary or secondary. For secondary XML indexes, the subtype (for path, for value, or for property) can be specified. For primary XML indexes, this specification can not be added.

SPATIAL indexes have various options to specify exactly how to build the index, but the FOR HEMISPHERE option does not exist.

References:
CREATE INDEX:

SQL Server Execution Plans

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!


Featured Script

Count of Objects by Database on a Server

I needed to get a count of objects by database on a server to assess which dbs are good candidates for Red Gate SQL Source Control.  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

Unexpected Database Growth ?? - Hi , Last week[Thursday] the d size is arround 30 GB. But today i wondered the database size is around 47...

Health Check script for sql server 2005 - Hi Experts, need a small help as i want to do health check on my sql server 2005, i was wondering...

Agent Error log - Hello, I cant access the old SQLServer agent error logs. Through SSMS neither through query xp_ReadErrorLog 2, 2, NULL, NULL Error is [b]Unicode...

objects never being used -? - Looking over different forums i slightly tweaked the query to identify a object (table/view) on which a select ,update,delete,truncate has...

SQL Server Login/Logout Audit - I know i can audit the time when a user logged in and also for failed logins. My goal is...

Update over linked servers slow - HI, I need to update a tabe with joing to remote server. If I run the query from the source server,...

SQL Server 2005 : Backups

sql 2005 and logshipping - hi, I have a basic question since years ago now, I did not find any solution. One database on one server name...

Maintenance cleanup task not deleting old files - Hi folks, I have created some cleanup tasks within maintenance plans to clear old backup and TRN files and nothing seems...

SQL Server 2005 : Development

Fragmentation of Clustered Primary Key - I have a table which has a clustered primary key on one int column, which is an identity (1, 1)....

Pivot query - I have the following data... [code] uid fieldid vaue 1172 5000 1172 5001 Development,support,management 1172 5002 1172 5003 Other 1172 5004 Computing 1172 5004 Medical 1172 5005 Networking 1172 5005 Consulting Projects 1172 5006 Chicago 1172 5009 [/code] The following code... [code] select userid, [5001] as...

SQL Server 2005 : SQL Server 2005 General Discussion

Error with DBCC CHECKDB - I am getting error while executing DBCC CHECKDB on one database that shows [b]CHECKDB found 0 allocation errors and 1...

Is it me or is it them? :-) - Hi, I'm just looking for either justification that I'm not going crazy and know a little bit about SQL, or...

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

Database snapshot takes too long to create - I am mirroring a SQL 2005 database and have a snapshot scheduled on the mirror every hour. This normally takes...

SQL Server 2005 : SS2K5 Replication

Creating Alert and Notifications for Replication Issues on SQL Server 2005!!! - Hi All, I have been assigned a Task to set up Alert and Notifications for all the Issues arising in Replication...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Catalog Manager\Cache Hit Ratio - One of my colleagues has a client whose DBA isnsists on 90% value for the performance counter - SQL Server: Catalog...

SQL Server 2005 : SQL Server 2005 Integration Services

switch to unicode - I have about ten SSIS packages that use varchar and char columns in tables. Most are built from the wizard. Is...

How to read data in a pdf file in SSIS - I was wondering if any body had a situation where data needs to be extracted from pdf files and exported...

can SSIS be part of cluster - I have read in some MS article that its not advisable to cluster SSIS. My customer has a requirement to do...

SQL Server 2005 : T-SQL (SS2K5)

hierachy with miltiple parent - Childid Childname Parentid pid 100 Bingo 200 1 101 Pingo 201 1 102 Zingo 201 1 100 Bingo 201 2 101 Pingo 200 2 102 Zingo 201 2 100 Bingo 201 3 101 Pingo 201 3 102 Zingo 200 3 100 ...

Remove redundant data from address column - Hi, Consider the folllwing two records: [code="sql"]if object_id('test') > 0 drop table dbo.test create table dbo.test ( master_id int, Title varchar(20), Forename varchar(50), Surname varchar(50), Address1 varchar(200), Address2 varchar(200), Address3 varchar(200), Address4 varchar(200), Town varchar(100), County varchar(100), Postcode varchar

SQL Server 7,2000 : Administration

retrieve / recover sa password - Hi all! We just finished installing our new server with SQL Server 2005. One of our older applications is apparently

SQL Server 7,2000 : Data Corruption

The header for file"dbname_log.ldf" is not a valid database file header - Please help me with this problem. SQL 7.0 I copied both ldf and mdf file after shutting down the SQLServer because of...

Suspect Mode - Good morning, It is a bit of a long story but i'm recently hired and have had a sql server that...

SQL Server 7,2000 : General

SQL entrance exam - Has anyone taken an SQL server entrance exam for Scott Trade? I'm not sure what to study or how to prepare....

SQL Server 2008 : SQL Server 2008 - General

SEPARATE WTH HYPHEN TO STRING - HI ALL I HAVE STRING SUPPOSE STRING :- AD675498IJU76 I WANT TO SEPARATE WITH HYPHEN (-) BUT EVERY TIME POSITION IS NOT FIX. ...

Database Transactional Replication - Good Day, we experienced one of the dreaded messages using Redgate Transactional Replcation between 2 SQL Server 2008R2 : SQL Error: Violation...

Can anyone explain me this query? I never used for xml path() before - select ','+quotename(C.name) from sys.columns as C where C.object_id = object_id('[CUSCONFIG].[LSCIENCES_C20_INT_GBU_REQUESTDETAILS]') and C.name in( 'CustomAttribute1','CustomAttribute2','CustomAttribute3', 'CustomAttribute4','CustomAttribute5', 'CustomAttribute6', 'CustomAttribute7', 'CustomAttribute8','CustomAttribute9' ) for xml path('')

Replication failing with Merge process could not enumerate - Hi All, Out of the blue my SQL server started going slow sporadically and every so often throws up some errors. SQL...

Replication Issue. Inserting through a Trigger. - Hi All, I just setup the replication in my environment. Table structure. CREATE TABLE [dbo].[pwallet_tran]( [TId] [int] IDENTITY(1,1) NOT FOR REPLICATION...

How to get multiple records from table into free form report layout - I have designed free form layout and depending on date range provided as parameter should get individual record on each...

insert data into table from XML File - Can any body help me in inserting data into a Table from this XML [code="xml"]<?xml version="1.0" encoding="UTF-8"?> -<LabReport> -<ReportHeader> <Requested-By/> <Requested-On>08/05/2012 11:13...

Importing CSV in chinese language into Sql Server 2008 R2 - Hi, I have a CSV file which has data in chinese language. I am able to insert the data from CSV...

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'list'. - use master go EXEC sp_configure 'show advanced options',1 go RECONFIGURE EXEC sp_configure 'xp_cmdshell',1 go RECONFIGURE declare @FolderTo varchar(250)='c:\SQLDATA'CREATE TABLE #x(dbname sysname,fileName sysname,location varchar(200)); get...

Can we create clustered index on non primary key column - Hi Guys...Please let mi Clarify The is it Possible to ' Can we create clustered index on non primary key column...

error Must declare the scalar variable - ALTER PROCEDURE [dbo].[sp_createdate2] @sdate AS date , @edate AS date, @userid AS INT AS BEGIN DECLARE @FirstTable TABLE (id INT IDENTITY NOT NULL PRIMARY...

Error: Must declare the scalar variable "@UPDATE". - Hi, 1)How to resolve this problem " Must declare the scalar variable "@UPDATE". myConnection = db.getConnection(); DataSet ds = new DataSet(); SqlDataAdapter myAdaptor = new...

how to add a grand total column to pivottable - I would like to add a grand total column (total number of students) to a pivot table the pivot table...

Unable to connect to one or more Databases - Hi SQL Masters, I have a situation here regarding my supported two Databases, one of them is ABC Database and the...

code for calling a stored procedure called spDateRange - Code a call to the stored procedure That returns Invoices with an InvoiceDate Between April 10 and april 20 2008...

Summing 2 Dates but only Workdays - Thanks for the help yesterday. Today I am looking to sum 2 dates but only workdays. So if I have...

Suggestion on how to implement a custom rollback - Hello Experts, I am trying to come up with a better solution for our intranet web application (running on SQL Server)....

How to I add column for a table online? - Hello! I have a database with a big tabla (600 millions row). My task: add 3 column for this table online....

stored Procedures - I could really use some help with this! Create a stored procedure named spBalanceRange that accepts three optional parameters. The procedure...

List of all numbers between low and high - What is the best way to get a list of all numbers between low and high numbers? Thanks. Actually >= low,...

Variable Cannot Be Bound in SSIS Package - Hey All, I am trying to insert a normalization procedure into SSIS package so that when I load from the...

How To change data Inside Text File - HY00980000780000000000000000000000000000000000000000000 GH00001200000000000000000000000000000000000000000000000 GH00001200000000000000000000000000000000000000000000000 GH00001200000000000000000000000000000000000000000000000 GH00001200000000000000000000000000000000000000000000000 GH00001300000000000000000000000000000000000000000000000 GH00001300000000000000000000000000000000000000000000000 G

SQL 2008 R2 - peer to peer replication - Hi, We are setting up a POC of SQL replication between two indentical SQL server (including hardware). The concurrent updates (same...

can any one plz give some idea about SQL Server Integration Services (SSIS)? - here i am having two database and i want to mapp the data related to that tables

Changing No. of CPUs when SQL Server 2008 already installed - Hi, I've a Win Server 2008 machine on which SQL Server 2008 is installed. Now I need to reduce the No. of...

increasing duration for the same query over time - Please excuse the double post for very similar question- I think I was on the wrong track with my previous...

SSIS Sharepoint List Source Timeout Exception - Hello All: I have an SSIS package with SharePoint List adapter as data source connection. I'm experiencing the timeout issue very...

Remote / Local node page looups/sec both zero - When I try to monitor the sys.dm_os_performance counters "SQLServer:Buffer Node\Local node page lookups/sec" and "SQLServer:Buffer Node\Remote node page lookups/sec" they...

Service Broker External Activation - I'm investigating Service Broker External Activation for one of our products. We would like to be able to drop items...

Snapshot dropped, spares files remain... - Application folks have a process by which a job drops and creates database snapshots. In one particular, odd case, a...

SQL Server 2008 : T-SQL (SS2K8)

Practical Used Of CTE - Hi all experts, Just happen to read about CTE. What i can understand from the stuff given on internet is that...

Date Format Problem - Hi, I have a little problem where my Database stores Dates in 'yy/mm/dd' format. I want to retrieve Date in 'dd/mm/yy' Format. I...

Partitioned Table performance Question. - I've been having a look at a project that is underperforming when selecting from a view, and noticed that in...

Convert CHAR to DATETIME - Hello everyone, First of all, I hate working with dates, hahaha, hum... I checked all the topics related to my "problem"...

How to search in 100 stored procedures - Hi , Suppose i have 100 stored procedures. Now i want to search for 'abc' in all the 100 Stored Procedure....

Partition Function - What is partition Function and what is the used(need) of partition function?

How do I remove nulls from case statements with date fields? - How do I remove nulls from case statements with date fields . For example in the else clause when blank, it...

0 is equal to zero length string. Can someone explain how this can be? - --I know this has to do with an implicit conversion of the varchar to int, --but why does the zero...

Pivot And Unpivot Table - Hi, I am a newbie in sql server.I have came across various article on internet which shows how to implement pivot...

Addition Of Digits - Hi, This was the question ask to one of my friend during an interview. He was ask to perform the addition...

Identifying XML Node Position in SQL Server 2008 - I am having a problem identifying the node position that contains a particular element in a piece of XML. In the...

Intersecting Date Ranges - Hi, Today I was asked what the fastest way was to find out if a set of date ranges intersect. To clarify,...

WHERE 1 = 1 - I am afraid I am really going to show my newbie-ness with this question … I see lots of stored procedures...

SQL Server 2008 : Working with Oracle

ORACLE TEMP DB FIX Size - Hi, if I crate the default TEMP DB and declare the tablespace as temp I have no chance to create...

SQL Server 2008 : SQL Server Newbies

SQL Server 2008r2 - log free C:\ drive space to a dB Table - run nightly job - Preface: Using MS Access 2010 DSN-Less to SQL Server table. SQL 2008r2 RTM on Virtual Server with only a C:\...

SQL Server 2008 : SQL Server 2008 Administration

TempDB Performance - Hi All After searching online for troubleshooting TempDb contention issues, I found the following script: [code="sql"]Select session_id, wait_type, wait_duration_ms, blocking_session_id, resource_description, ResourceType = Case When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description,...

Sending email from SQL - Hi all, I am having trouble in sending email from db. I am not able to send emails to yahoo or...

High Memory usage - Hello, one of our Prod sqlserver is taking 6.5gb memory out of total 8gb of the system.It is 2008R2 64bit standard...

multiple instance on same server? - Hi, First I had installed one SQL 2K8 with default instance on server, I need installed one more named instance on that...

Question about SQL Server, Memomy, Disk, Configuration. - Hi all, i cant think about an apropieted subject for the topic. Here is the thing. We have a Server with this...

Violin Flash Memory Array - Hi - Currently, I'm using a substantial number of FusionIO cards on our production environment. We're migrating to new hardware and...

Rolling back Service packs (SP2 to SP1) for SQL Server 2008 - Can anyone please suggest me any method to rolling back "SQL Server 2008 SP2" to "SQL Server 2008 SP1". I...

Hash Partitioning - Hi All, I am in situation where I am not sure how to divide / Partition the table, and send the Data...

Career : Certification

MCSA & MCSE SQL Server 2012 !? - Let me know if somebody has the experience with MCSA and MCSE Certifications/ Exams!? Ok, I'm asking for the all information...

Career : Resumes and Job Hunters

How can I attract Senior Database Administrator candidates? - I have a few full-time DBA positions open on my team, one of which is for a Sr. DBA. We...

Programming : Connecting

MySQL as linked server - Hi everbody I need some suggestions I Have a sql server(2005) default instance in Windows Server 2003, I need to connect...

SQLServerCentral.com : Anything that is NOT about SQL!

The SQL Saturday Thread - I figured I'd start a thread (I don't think anyone's done so) for all things [url=http://www.sqlsaturday.com/]SQL Saturday[/url]! I'm heading down to...

Discussion: is e-Commerce making a mockery of SQL Server? - Some of you may have heard of my latest tales of woe, in that I am in an environment that...

The Beer Cooler Thread - On popular request - at least one! - a dedicated topic about beer. I shall start with my favorite beer: Duvel. It is...

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

How to SEND a report via SPROC - Hello, I am trying to build a dynamic stored procedure to send a report when called. I would like it to...

Reporting Services Parameter Bar Customization - Is it possible to customize the parameter tool bar in reporting services. By default "View Report" button is shown on...

Reporting Services : Reporting Services 2005 Development

SSRS 2005 Display Columns in a matrix report even if no data is present in the sql table - Hello, I have created a report in SSRS 2005 which contains a Matrix and I want to dislplay the following information: Row:UserID,...

Database Design : Design Ideas and Questions

Schema binding across multiple databases - Hi All We need to create a clustered index by implementing schema binding in the view. As you know it throws...

Data Warehousing : Integration Services

Executing Child Package from Parent - Unpredictable times - We have recently migrated from a Physical 2005 server to a 2008 R2 VM server. We started to notice something...

SSIS package - Column Truncation error at OLEDB Source output - Hi all, I created a package using oracle database as source. SSIS reads the columns in oracle in a Unicode format....

Data Warehousing : Strategies and Ideas

Show Right Column to Right User - If I have three different user with different occupation (manager, salesman, accounting) The main question is to how display right column...

Data Warehousing : Analysis Services

Question on Cube Design - Hi, I am kind of newbie on OLAP and I have some questions. I have about 6 tables in my OLTP database...