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

Who Inspires You?

Today we have a guest editorial from Chris Shaw as Steve is on holiday.

Between sessions at the 2013 PASS Summit held in Charlotte, NC. I was approached by an attendee who told me their accidental DBA story.  Their path to becoming a DBA was similar to that of many DBA’s that I have talked to in the past.  This attendee in particular had attended a SQL Saturday event where I had spoken on disaster recovery. She sought me out to tell me how my session had an impact on how she went about building her own DR strategy.

When I deliver a presentation, I focus on my primary goal: to help others navigate difficult decisions by hearing my successes and failures.

When considering my goal it is easy to see my presentations are based on experiences that I have had, often bad ones. Often is it difficult to share the failures but without them I can’t share the successes. The result is a session with a very personal touch. When I have someone send me an email or reach out to me at an event, and they tell me I have helped them, it really encourages me to do more.  Motivation to the point that I often forget that I am struggling to keep my eyes open during the long days at conferences.  Hearing I made an impact on just one person changes my view on so many things.

Does someone inspire you?  Who has helped you navigate difficult decision that you have made? Those that have inspired you may not know that have had any impact at all. They may be on the cusp of questioning themselves or a decision they have made.  It may be a point in time of that person’s career where they need to make a decision, and hearing from you could help them.  I challenge you to take the time today, just 5 minutes to send them an email, or stop by the local grocery store and pick up a card for them.  Encourage those who have encouraged you. 

Chris Shaw from SQLServerCentral.com

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

ADVERTISEMENT
SQL Monitor Hosted

Try new SQL Monitor Hosted

Get real-time server performance updates and instant access to the data you need to fix the problem, whilst we take care of the monitoring software. Get started now.

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.

Fundamentals of SQL Server 2012 Replication

Get to grips with SQL Server replication

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

Featured Contents

 

Locking, Blocking and Deadlocking

Wayne Sheffield from SQLServerCentral.com

Introduction into Locking, Blocking and Deadlocks in SQL Server, and why SQL Server uses these actions. More »


 

SQL in the City Seminar Washington DC 2013 –Deployment Stairway

Press Release from SQL in the City

Join Red Gate for a free seminar on December 6 (the day before SQL Saturday Washington DC). Steve Jones and Grant Fritchey, SQL Server MVPs, will present best practices for SQL Server version control, continuous integration and deployment, in addition to showing Red Gate tools in action. More »


 

Windows Azure SQL Database (WASD) Primer

Additional Articles from Microsoft MSDN

Are you a SQL Server DBA who is now being asked to migrate databases to Windows Azure SQL Database (WASD)? Are you a developer who is writing code for a cloud service that shall use SQL Database as the back-end? This blog post is based on issues encountered while supporting customers that were once in your shoes and the lessons learned in the process. More »


 

From the SQLServerCentral Blogs - MDX+SSRS #33 – Retrieve manager’s key and fully-qualified member name

Sherry Li's BI Corner from SQLServerCentral Blogs

A co-worker recently worked on a SSRS report and wanted to use a specific manager’s team for testing. The Manager... More »


 

From the SQLServerCentral Blogs - For the Aspiring DBA

Grant Fritchey from SQLServerCentral Blogs

Getting started as a data professional is an incredibly daunting task. If you’re not concerned that you’re going to mess... More »

Question of the Day

Today's Question (by Steve Jacobs):

What is the resultset from the query below?


--Query 1
CREATE TABLE #temp ( a CHAR(1), b VARCHAR(10) );
--Query 2
INSERT INTO #temp
        ( a
        , b
        )
        SELECT 'A'
              , NULL
        UNION ALL
        SELECT 'A'
              , 'Hello'
        UNION ALL
        SELECT 'C'
              , '12345'
        UNION ALL
        SELECT 'B'
              , 'Hello'
        UNION ALL
        SELECT 'D'
              , '54321'
        UNION ALL
        SELECT 'E'
              , NULL
        UNION ALL
        SELECT 'D'
              , '11111'
        UNION ALL
        SELECT 'B'
              , '54321'
        UNION ALL
        SELECT NULL
              , NULL;
--Query 3
SELECT a
    FROM ( SELECT a
              , b
            FROM #temp
         ) AS t1 PIVOT ( MAX(b) FOR b IN ( #temp ) ) AS t2
    ORDER BY CASE WHEN a IS NULL THEN 1
                  ELSE 0
             END;
--Query 4
DROP TABLE #temp;

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 2 points in this category: T-SQL.

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

Yesterday's Question of the Day

Yesterday's Question (by Raul Gonzalez):

In SQL Server 2008R2, if we create the following table, what will be the data type and nullability for the computed columns?


CREATE TABLE #t
    (
      col1 VARCHAR(8) NULL
    , col1Computed AS LEFT(col1, 4)
    , col2 VARCHAR(8) NOT NULL
    , col2Computed AS LEFT(col2, 4)
    , col3 CHAR(8) NULL
    , col3Computed AS LEFT(col3, 4)
    , col4 CHAR(8) NOT NULL
    , col4Computed AS LEFT(col4, 4)
    );

Answer: col1Computed varchar(4) NULL, col2Computed varchar(4) NULL, col3Computed varchar(4) NULL, col4Computed varchar(4) NULL

Explanation:
execute tempdb..sp_help '#t'
The data type of the Computed colums is determined by the function LEFT which will return VARCHAR when a NON UNICODE string is passed as parameter.
Although col2 and col4 are defined as NOT NULL, the database engine automatically determines the nullability of Computed columns based on the expression used.
The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows will produce null results as well. 
In this case, the function LEFT can return NULL values therefore the computed column is created as NULL

» Discuss this question and answer on the forums

Featured Script

Compare Current IDENTITY Value to DataType's Boundary

Arthur Gimpel from SQLServerCentral.com

Execute the script on the DB which contains IDENTITY property on large tables.

It will return data type usage percent of the identity seed per table, with the actual numbers included.

Note: The last statement querying the temp tables uses FORMAT() funcation which works only on SQL Server 2012+.

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 2012 : SQL 2012 - General

Problem changing account for job execution - SQL Server 2012, some jobs are centralized from another server running SQL Server 2008 R2. I'm having a problem getting jobs...

An error while copying database - I am trying to use Copy database wizard to copy databases from 2005 to 2012 sql server, but I'm getting...

SSIS Data Import - I have an external site that gets populated with csv files every Sunday. These files have varied file names depending...

Output a query as an Excel file - Hi all, I'm a newbie to SQL. I'm using SQL Server 2012 on my local machine and I need to find...


SQL Server 2012 : SQL Server 2012 - T-SQL

Coin combinations - There's an often mentioned "problem" which is sometimes used to help in teaching kids to understand combinations, but more often...

2 records at a time? - Hi everyone, I'm not even sure how to word this succinctly enough to search for this topic, so I apologize in...

Combine Multiple Queries into a CTE? - Hello all, I am looking to build a query with a result set that combines multiple queries to appear as...


SQL Server 2008 : SQL Server 2008 - General

Write to text file - Hi Professionals I am trying to write a variable with column headings to a text file but it does not seem...

how to hide schema? - hello :) please help me. i am not DB admin, and have no idea how to solve my problem: in a...

Cursor fetch loops endlessly - I am having a cursor which I use to update a 2nd table containing unique IDs from an identity column. The...

how to handle deleted data of a website - Hi, Recently, I have started work on a database of a online product based website. We add thousands of products every...

Need to replace HTML encoded special characters and language charcarters in tables - Historically entries have been stored in a DB with web styling encoding and are similar to these (i know bad...

Transactional Replication Performance Issues After Migration From 2000 to 2008 - We currently have 140 SQL 2000 publications replicating to a single 2008 server with about 25 articles. 5 of the...

Select Statement Sum Question - I have the query below, I'd like to display the sum([Inbound Calls]) and Sum([Accepted Calls]) both grouped by [MonthID], along...


SQL Server 2008 : T-SQL (SS2K8)

delete certain nodes - How do you delete

T-SQL Query - Hi, I have a table which has the data shown in the attachment(Table Data). I need the expected result. Please suggest. Cheers Jim

T sql for patient arrivals, transfers In and Out and discharges and census on a hospital floor by hour - Hi, I am trying to create a SP which lists the total number of patients Arrivals, Total Discharges,Total transfers and total...

Need Help - Hi Friends, I need help to get one (Identity_id) value from XML, I wrote below script but I am getting...

Most efficient way to get a single unique combined record for distributed information corresponding to same ID - I have a scenario like this: [code="sql"] create table #temp(id int, name varchar(50), age int, contact_number varchar(50)) insert into #temp(id,name) select 1,'John' go insert into #temp(id,age) select...


SQL Server 2008 : SQL Server Newbies

sql server agent won't start - SQL Server 2012 SQL ServerAgent is currently Stopped. I want it to be running, so I right click and select start...


SQL Server 2008 : SQL Server 2008 High Availability

log shipping setup advise needed - Hi, I was wondering if experts here can help with an advise :-) Let's say I have a site A with...


SQL Server 2008 : SQL Server 2008 Administration

Killing a transaction - Hi, I have a questions related to KILLing a transaction. 1. Can i directly Kill a transaction if I found it is...

Service Packs for SQL 2008 R2 - Can you please tell me any one what are the Service Packs are available in SQL Server 2008 R2 ? There...

Corrupt Backup File ? - I got this message when trying to restore a DIIF backup Msg 3183, Sev 16, State 2, Line 91 : RESTORE detected...


SQL Server 2005 : Administering

Always save query to file - Hi, I would like to know if there is any property in Management Studio that I can change to save automaticaly...

Regarding SQL Server certification. - Dear Experts, I have passed my MCTS (70-431) exam. I would like to do more certifications on SQL Server. Can anyone guide...


SQL Server 2005 : T-SQL (SS2K5)

query logic question... - currently i have a table that has the following layout, exact details of table not needed. table1 pkey, mf_1 char(5), mf_2...


Reporting Services : Reporting Services

Retrieve Error message of Permissions in SSRS - Goal: To make the user sara to have full access to all document in report server. Problem: I retrieve the error message: "User...


Reporting Services : Reporting Services 2008 Development

Preview report without savings - Hi, I use SSRS 2008 R2 Developer. I want to change a report and preview changes but then close without saving them. The...


Data Warehousing : Integration Services

Error: System.Reflection.TargetInvocationException - hi guys please help me with my package, its a very simple package which emails supplier when the work order reaches...

Maximum size of SQL variable in SSIS ? - I have a string variable which stores a big set of sql statements, ie insert statements. The insert statement intends...

Using Lookup transformation or Full load - Hi Experts, I need to load all the records that are new or updates for Oracle source to SQL Server table....

From Oracle to SQL Server real time - Hi When certain data change in a Oracle table, I wish to move the changed data to a SQL Server immediately. Can...

Create table in MS SQL Server ffrom Quickbook - Hi Experts Can anyone please guide me how to create destination dynamically as its from Quickbook Source table.In Quickbook i...


SQLServerCentral.com : Anything that is NOT about SQL!

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

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

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