In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Data Compare Save time when comparing and synchronizing database contents
"It has also streamlined our daily update process and cut back literally a good solid hour per day." George Pantela, GPAnalysis.com. Download a free trial of SQL Data Compare now.
 
SQL Monitor Get alerts within 15 seconds of SQL Server issues
SQL Monitor checks performance data every 15 seconds, so you can fix issues before your users even notice them. Start monitoring with a free trial.
 
SQL Prompt Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.

In This Issue

Stay Backed Up by Monitoring Your Backup Process

Based on a linked server setup, you can check your SQL Server backups on multiple instances. More »


Diagnosing Common Database Ails

When a database starts showing signs of an illness, it's up to the DBA to get to the root of the problem, fast. Kat Hicks takes a look at the most common causes of database troubles, free tools that can help, and the misconceptions that get in the way. More »


From the SQLServerCentral Blogs - Staggering SQLIO Sessions Across Multiple Volumes

In a previous post, I discussed running SQLIO against multiple files.  The purpose in using multiple files is being able... More »


From the SQLServerCentral Blogs - SQL Server : Part 10: Importance of Key Column Position While Creating Index

Now we have discussed about different types of indexes in the last posts. In this post, let us discuss about... More »


Editorial - A Billion Transactions

How long would it take your systems at work to process a billion transactions? You'd expect some, heavily used and highly visible systems to be involved. The stock market systems process billions of trades a day, but I'm sure most of the systems in single companies, even large companies, deal with fewer transactions on a daily basis. A billion transactions a day is 11,000+ transactions a second, sustained across the entire day. That's a heavy load, but it might be the level of transactions that more and more of us will see over time as our systems gather more data.

The Microsoft corporate headquarters in Redmond consists of over 100 buildings on 500 acres. It's grown over the years from its original 88 acres, which is also the title of a story about Microsoft and the relatively unknown work in automating their infrastructure. Not the computer systems their software developers use, but rather their facilities and physical buildings. It's a fascinating story that outlines the way in which Microsoft saves millions of dollars in maintenance and repairs by using software.

Across those buildings, Microsoft collects a huge amount of data, from disparate systems, which is the presented to the facilities personnel. The sensors and systems don't process a billion transactions day; they process half a billion. Still an amazing amount of data, just from physical buildings and the infrastructure that ensures Microsoft employees have a pleasant place to work every day. Using a combination of SQL Server, Office, and Azure, Microsoft has built a software system that corrects many faults itself within sixty seconds. Those that can't be fixed remotely often end up generating one of the 30,000 work orders produced for personnel every quarter. The system is forecasted to save 6-10% of the energy that might otherwise be wasted with a less efficient system.

It's a great read, and perhaps is a good case study for an application that is well suited for cloud services. There are a few great quotes from the article as well that are particularly pleasing to a data professional. "Give me a little data and I’ll tell you a little,” he (Darrell Smith) says. “Give me a lot of data and I’ll save the world." That ought to be the model for data analysts. As SQL Server professionals and developers, we should be helping others to do just that.

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

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


Question of the Day

Today's Question:

Given this table and data:

CREATE TABLE #Test
( ID INT
, IsAvailable INT
);

-- Insert into table
INSERT INTO #Test (ID, IsAvailable)
SELECT 1, NULL
UNION ALL
SELECT 2, 1
UNION ALL
SELECT 3, 1
UNION ALL
SELECT 4, NULL;
GO
If I want this output:
ID IsAvailable
1  Apr  9 2013 11:38AM
2  1
3  1
4  Apr  9 2013 11:38AM
Which query will give the correct output?

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


Yesterday's Question of the Day

When running the code below on SQL Server 2008 and above, what will the final query (with the comment "Predict the output of this query") return?

CREATE TABLE dbo.Test
   (PrimKey int NOT NULL,
    ValueCol int NOT NULL,
    PRIMARY KEY (PrimKey)
   );
go
CREATE TRIGGER TestTrig
ON dbo.Test
AFTER INSERT
AS
IF EXISTS(SELECT *
          FROM   inserted
          WHERE  ValueCol < 0)
BEGIN;
  RAISERROR ('Negative values are not allowed!', 16, 1);
  ROLLBACK TRAN;
END;
go
-- Insert attempt #1
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (1, 1);
-- Insert attempt #2
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (2, -2);
-- Insert attempt #3
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (3, 3), (4, 4);
go
-- Predict the output of this query:
SELECT COUNT(*) FROM dbo.Test;
go
DROP TRIGGER TestTrig;
go
DROP TABLE dbo.Test;
go

Answer: 1

Explanation: When a trigger rolls back the transaction, the batch is aborted with error 3609. So the rest of the batch does not execute, and the client can start sending the next batch.

In the case of this question, insert attempt #1 succeeds (as there are no rows inserted with ValueCol below zero). Insert attempt #2 fails (the negative value in ValueCol causes an error message and a rollback). This aborts the batch, so insert attempt #3 is never executed.

The SELECT query is preceded by "go", so this is a new batch and will execute. Since insert attempt 1 had success, insert attempt 2 was rolled back, and insert attempt 3 was not executed, the number of rows in the table will be 1, so that will be the output of this query.

Reference: http://www.sommarskog.se/error-handling-I.html#triggercontext

» 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

fnArabToEnglish(from Arabian number to english)

This function can convert a Arabian number to english,for example,if you input 1234.56, then you'll get 'One thousand two hundred thirty four and cents fifty six'.  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 : Backups

Log backups while Full/Differential backups are running - A few weeks ago I was at an event where someone came up and asked me if it's bad to...

SQL Server 2005 : SQL Server 2005 Integration Services

store the select query result as variable and load on another table in ssis - Hi guys ... Like to know how i can store select query result in variable and then load it to the...

dynamic excel columns into dynamic table - I have excel with dynamic column and i want to load into table creating dynamic columns. I need to add new...

SQL Server 2008 : SQL Server 2008 - General

How to fix Filestream enabled database - I have SQL Server 2008 R2 FileStream enabled Database, when I try to access any value that stored in the...

Schemas and Roles - Hi, I am confusing about the usage of roles and schemas. I have done some research on Internet, and I found...

i need .bak files - Hi, i need .bak files for testing purpose. where can i get that files........ Regards, shiva

Very interested to know what can be done in the case of a long running select query - Hi everyone. I'd love to get some help understanding why SQL Server 2008R2 x64 Enterprise Edition does what it does. The...

Help with Triggers - I have to create 3 triggers for a table - INSERT, DELETE, and UPDATE. The triggers will be used to update...

What happens when there is update to rows (in terms of database internals) - Hi, What happens when there is update to rows (in terms of database internals)? Thanks

Huge log backups after Ola's defrag and maint script runs - I have a SQL 2008 R2 instance that's hosting several OLTP databases for LOB applications. Every night Ola Hallengren's index...

help searching multiple words - hello, i am trying to make a procedure that returns values if and only the query matches all words .. EX: [code="sql"]declare...

Fixing Divide by Zero Error - Hello, I have in select statement select t1.A/t1.B but get divide by zero error when I used with isnull isnull(t1.A/t1.B,'-') I get error...

SQL Server 2008 : T-SQL (SS2K8)

Generate SSRS Report and then Email Report from a single Stored Procedure - Can someone please offer assistance? I need to create a Stored Procedure that will generate a SSRS reports a number...

convert tSQL mySQL to MSSQL - Hi Guys need some help with converting this part of a query from MySQL, I need to run this on...

Find Serial number in sql string - So i need to query Active directory and get all the computer based on certian OS type, no problem doing...

Storing dynamic sql - DECLARE MY_CURSOR Cursor FOR SELECT [DB_NAME],[SCHEMA_NAME],[TABLE_NAME],[COLUMN_NAME] FROM dbo.Data_Profile_Stats Open My_Cursor DECLARE @DB_NAME nvarchar(500),@SCHEMA_NAME nvarchar(500),@TABLE_NAME nvarchar(500),@COLUMN_NAME nvarchar(500), @QUERY NVARCHAR(MAX) DECLARE @MAX_NUMBER INT, @MIN_NUMBER INT, @NULL_COUNT...

filter duplicate students via T-SQL - /* I need to filter for duplicate students: --------------------------------------- For same student, if one or both instance/s of IDNo is null then do...

tsql query - Count the number of spaces in a string - How do I write a query that tells me how many spaces (...or any character for that matter) are in...

SQL Server 2008 : SQL Server Newbies

Combining Multiple Tables Having Count - Hello, I am trying to combine multiple tables, and then show only when value [Qry] when it is greater than 1...

Slows system performance - Dear All I am running a process (updation/deletion/insert) of 2 million records. After some time this process makes my system very...

SQL Server 2008 : Security (SS2K8)

Stored Procedures Execute Permissions - My global office programmed all our stored procedures and it contains a lot of create/delete temp tables, truncate tables, update,...

SQL Server 2008 : SQL Server 2008 High Availability

Log Shipping Failover Test - Hi I'm currently running some DR testing and have failed over a couple of databases to our DR site. Generally the...

SQL Server 2008 : SQL Server 2008 Administration

TempDB on Amazon Ephemeral drives - Has anybody used the EC2 Ephemeral drives as a location for their TEMPDB databases? Normally I use a standard volume but...

ssas understanding - I am currently working on SSAS 2008.Gone through ebooks for understanding but never got proper indepth understanding of dimensions and...

Break the replication - I have configured the Transaction replication Here i want to know how to break the transactional replication and i want re...

Transactin Log Issue - Hi, I am having issue with out Transaction logs as it's keep getting full even after shrinking the file. We have Sql...

No performance gain on queries on different servers with considerable hardware change - I have a dedicated Server for SQL Server. SQL Server runs on SQL Server 2008 Standard edition with SP2 applied....

SQLServerCentral.com : Anything that is NOT about SQL!

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

Textbox fill color based on textbox value - Hello, I have a report with a series of textboxes. The value of the textboxes is based on the month and...

Regex Expression to remove RTF tags - Hi There, I have a file that I get from pulling out values from a Microsoft Lync 2013 conversation that has...

Data Warehousing : Integration Services

Excel Source DT_NTEXT Problem - i am trying to load the data from excel files and facing problem in Text data. Excel source in SSIS tries...

Data Warehousing : Analysis Services

Retrieving the ALL member only - Hi, I have a problem with retrieving the ALL member. With MDX executed from SSMS, it returns the 'All' value into a...