In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Data Compare logo 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 Backup Pro logo "Impressed is the word!
Cut our backup times in half and reduced the space used by 80%!" Tobie Dunn, SQL Backup Pro 7 user. See what savings you can achieve - download a free trial today.
 
SQL Skills logo Deep technical training by world-renowned experts in 2013.
You can't get better ROI for your training budget. Read more.

In This Issue

Advanced Logging - Level 12 of the Stairway to Integration Services

In this level, Andy Leonard goes into advanced logging techniques for your packages. More »


SQL Bits XI

The popular UK conference heads to Nottingham from May 2-4, 2013. Both Grant Fritchey and Steve Jones will be speaking, along with lots of talented SQL Server professionals. Register today. More »


Precision Indexing: Basics of Selective XML Indexes in SQL Server 2012

Seldom has a SQL Server Service pack had such an effect on database development as when SQL Server 2012 SP1 introduced selective XML indexes. These transform the practicality of querying large amounts of XML data. Seth Delconte demonstrates how and why this feature makes all the difference.  More »


From the SQLServerCentral Blogs - Determining Filegroup for a Table

Have you ever needed to figure out which filegroup your tables are located within?  If you had to do this,... More »


Editorial - Data Journalism

The open data movement in goverment has produced some amazing data analysis from many sources. Many people are taking freely available data sets and producing a visualization, or an analysis of a problem, or even an application that is useful to the public. It's one of the ways that technology and data analysis has really changed the world in a way that wouldn't have been possible before powerful computers and mobile devices.

I ran across a piece on data journalism that talks about a few projects around the world. This is the idea of adding a story, along with context and clarity, to facts. That is what many people are showing in the various projects in the O'Reilly piece, and it got me thinking. Perhaps this isn't just something that can be done with open data and public services. Perhaps this is something we could be doing more of within all our organizations.

Journalists learn to inform people in a compelling way. Data journalism is based more around large sets of data. Most of the people I know working with SQL Server often understand the data much better than the business analysts. These technologists, usually those performing some type of development tasks, learn how the data is structured and stored, and might notice the patterns and anomalies in ways that business users ignore.

As the future of databases and database workers evolves, I suspect that those people who can learn to tell a compelling story about the data, that can present facts to clients and customers in a captivating manner will be in demand by many employers.

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

Advertisement: Setup monitoring on of your SQL Server instances in minutes, with pre-configured alerts. SQL Monitor, from Red Gate, includes many very common monitoring metrics that you might not think to enable yourself. We also include support for any custom metrics you might wish to write, and a site at SQL Monitor Metrics that has code from many well known and respected SQL Server experts.

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:

Which of the following query will provide different answer? 

Statement A. SELECT (45 * (9 - 5 + 8)- 36 / 4)

Statement B. SELECT (45 * (9 - (5 + 8))- 36 / 4)

Statement C. SELECT (45 * (9 - 5 + 8)- (36 / 4))

Statement D . SELECT (45 * ((9 - 5) + 8)- 36 / 4)

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.

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Yesterday's Question of the Day

You run the following code in an SSMS query window in SQL 2008, 2008 R2, or 2012. The SQL server and SSMS are default installations (language English(UNITED STATES), collation Latin1_General_CI_AS) with default configuration.  The database PLAYPEN exists and its default collation is the server default.

declare @k int;
select
  case when @k=0 OR NOT (@k = 0)
    then cast(0 as datetime)
    else cast('2012-04-01T10:00:00' as date)
  end as [when] ;

What value does the select statement deliver for column [when]?

Answer: 2012-04-01 00:00:00.000

Explanation: A searched CASE expression returns the first value for which the test delivers TRUE, and the untested (default) value if there of the tests delivers true. The boolean expression "@k=0 OR NOT (@k = 0)" translates as "UNKNOWN or not UNKNOWN" since @k is null, and SQL's three-valued logic ensures that the value of this expression is UNKNOWN, not TRUE; this is the only test, so the ELSE (default) value, "@2012-04-01" is used to construct the result. But the result value has to be converted to the type with highest type precedence amongst all the possible results (ie all branches of the CASE statement) before it can be delivered, and DATETIME has higher precedence than DATE, so the DATE value of the else clause is converted to a DATETIME value and displayed as "2012-04-01 00:00:00.000".

References: Data Type Precedence, CASE

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Featured Script

Script to see Table Row Counts

Script to get #rows in each table 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

DB File Extension - I noticed that one database file does not have an extension and the type is just File. I have an...

Lot Of Blockings in production server - Hi, one of our production server we are getting lot of Blockings i found the blocked process i fired below queries i...

SQL Agent Job Sudden Failure, Login failed for user NT AUTHORITY\SYSTEM. - I have a scheduled job (runs every Friday at 5am) that has been running since 9/2011. Suddenly, the job failed...

update statisticsin sql - how long the update statistics job used to run on the largest database(of size 1300GB)

Can't start SQL Server service as Network Service - I have a development server that is running SQL Server 2005. I was asked to install a new instance for...

Issue in deadlock in our production server - We are facing dead lock issue on prod database, and checked sp_who2 lot of blocking is there, more than 25...

Delete with TABLOCK - [b]To delete rows in a heap and deallocate pages, use one of the following methods. Specify the TABLOCK hint in the...

collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. - Hi, We have sql server 2005 reporting services on server A & its databases on Server B. I'm seeing the below error...

error creating a linked server from sql server 2005 to 2000 - Ok, I'm trying to create a linked server connection going from a 2005 to a 2000 server. This is what...

Failover Alerts - Hello everyone ... i have been searching and searching for a good way to email alert me when my sql cluster...

SQL Server 2005 : Backups

Backup DB with no messages - Is there a way to suppress the backup messages during a backup? Processed 256 pages for database 'TestDb', file 'TestDB' on...

SQL Server 2005 : Business Intelligence

SSAS converts NULL values into string 'NULL' - SSAS converts empty fields into string text 'NULL'. I found out because my report shows NULL as string value. Running...

SQL Server 2005 : SQL Server 2005 General Discussion

help needed for sql query - I am working on sql 2005. I have a table wherein it maintains the history of status Customer Status CreatedDate LastUpdateDate CustA...

SQL Server 2005 : SS2K5 Replication

Editing replication stored procedures - Just wanted a high level answer really on whether or not it is a good idea to alter the sql...

SQL Server 2005 : SQL Server 2005 Integration Services

DTExec succesfull in CMD window, fails in Agent Job - I am tasked with processing an excel file and the file is password protected. To do this, I have a...

File System Task > Move Files > Error - Process cannot access the file because its being used by another process - Hi, I have 2 foreach loop containers within my control flow: The first foreach loop container loops through a directory and loads...

Problem referring to a SSIS variable inside Script task - Hi, I have a very simple Foreach loop container, where it specifies very simple Foreach File enumerator and refers to a...

SQL Server 2005 : T-SQL (SS2K5)

Explicitly defining namespace in For XML query - I need to mimic the Namespace of a legacy app exactly. It should look like this: <Customers xmlns="x-schema:D:\APPS\Customer.xdr"> </Customers> When I put in my...

Selection Of Sql Server Version - I am starting a very big project from the scratch.Till now i was using sqlser2005 with Visual Studio.Net2008....Now as my...

some one please help me to read this xml-data from a XML variable - HI, I have a @xml variable, I need to read this variable and to insert the values into my table in...

Database Design question regarding Normalized Tables - Hey all, Got a theoretical question for you guys. Let's say I have a table that contains information about a store....

Problem with Full Text Searching - I have one table in sql server say example Table Name candidates Test table contains some columns cid , cname,c_resume cid in Int...

SQL Server 2005 : SQL Server Newbies

How to add two column and the to put the result on the next row? - [center]column1--- column2--- result 50 ------ 1500----- 1550 100----- - 1550----- 1650 40----- - 1650----- 1690 30----- - 1690----- 1720 Now I do have the value...

SQL Server 7,2000 : SQL Server Newbies

Help Re Indexing - Hi, I have a 28 GB SQL 2000 Database that is in need of a re index. I am currently...

SQL Server 7,2000 : T-SQL

Count Decimal Places - i'm trying to count the number of decimal places in a field. e.g. mynumber decimal 9 (18,9) Len(mynumber) result = 11 I've tried...

SQL Server 2008 : SQL Server 2008 - General

"collapsed" by default - When we're using excel to browse a cube the dimensions and measures in the pivot table field list are always...

SSIS package fail - sometimes - Hi At last we ditch SS2000 and start new with SS2008R2 for our reporting database! I have a re-written job package running...

what will be the maximum value of Statistics Logins/sec - what will be the maximum value of Statistics Logins/sec?

Security folder in SSMS - In SQL server management studio there are two security folders.One under the server and another one under each database. I...

how to assess server landscape related to virtualization. - how to assess server landscape related to virtualization. what are the inputs that i need to consider

trace scrubbing tool - I need to find a trace scrubbing tool for my traces. the zip file at : http://msdn.microsoft.com/en-us/library/aa175800(v=sql.80).aspx does not download. Can anyone help!

How to use table valued function as part of select statement in sql server - I want to use table valued function as part of select statement in sql server. Below is example INSERT INTO @Table...

SQL Function takes more than 2 hours to return a table.. - I have a function with cursors which returns a table in SQL Server 2008. On executing the query which calls...

BACKUP failed to complete the command maintenance plan - Hi, Guys i schedule the backup using maintenance plan. Error is: 3041, severity: 16, state: 1 Error message is: BACKUP failed to complete the...

how to find result for this table - PaymentHistoryID paymentID Amount receivedType Reason 1 11 5000.00 Dr ASD 2 12 4000.00 Cr BG 3 11 8000.00 Cr BG 4 11 3000.00 Dr SD 5 11 3000.00 Cr ASD 6 13 50000.00 Cr ASD 7 13 50000.00 Cr ASD 8 13 1000.00 Cr ASD 9 13 500.00 Cr ASD This is my table……….. pls help me at vanapandi@gmail.com Payment ID Amount(dr-cr) Reason 11 5000-3000=2000 ASD 11 0-8000=-8000 BG 12 0

Are there tools for automating of SQL Databases and Applications deployment? - Are there any tools on the market for automating of SQL Databases and Applications deployment? I do not mean installation...

Problem with a subquery format - Hello all! I am trying to run the following query The requirements are to compare all the records created today vs C.dcreated...

Scalar function too slow - I created a function which basicallly takes the following input: [img]http://s12.postimage.org/xf1pjeuvh/tworecords.jpg[/img] then performs various checks on the name details to determine a...

Trigger to insert into a new table from a deleted row. - Hello 1.- What would be the code to insert some columns from a deleted record into a different table. 2.- Can I...

How to use Select within select in the same Select statement..?? - [font="Courier New"][size="3"]Hi All, I Have table where it includes fields model_size, model_type, actual_qty, process_qty, order_type. Now I want to display all...

Design Question - I seem to be struggling with something that I thought was fairly straight-forward originally when I started designing the database...

Advices for best practices for configuring MS SQL Server system databases? - Hi, I need some advices and best practices regarding the size of MS SQL Server system databases. We use MS SQL...

Create a Column That holds the SUM of values in the same field where The Invoice Number is the same - First, sorry for the confusing title. I am looking for a way to sum the values based on the customerid and...

Backup Time for 2 TB DB - I am using SQL 2008 R2 before I start backup want to know how long it will take to do...

SQL Login error from client computer - [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. I can not connect to the SQL from the Other PC which...

logical consistency-based I/O error - Hello, when i want to see properties of one data base ,or run maitenancetools or backup sqlserver give me this...

Unable to Upload Files into FILESTREAM col - Hi, I have successfully created a table with a column of VARBINARY(MAX) data type and its FILESTREAM is set. I then linked...

SSIS 2008 Script Component error - Hi, I'm using Script component in SSIS 2008. This is the error I received when I'm done writing the script. Microsoft.SqlServer.Dts.Pipeline.CannotCreateUserComponentException:...

SQL Server 2008 : T-SQL (SS2K8)

UNION vs OR --> NP-Complete Problem - I have recently read about this in the below link. http://www.sql-server-performance.com/2013/tsql-incorrect-union-operator/ I just heard about "NP-Complete Problem" first time :) [quote] From Joe Celko... This...

Delete from Two tables in Single Statement (without Cascading) - Table A ------------ ID NAME 1 'A' 2 'B' 3 'C' TABLE B ------------ ID NAME 1 'A' 2 'B' 3 'C' 4 'D' 5 'E' SAME ROW 1 'A' 2 'B' 3 'C' I want to delete...

sql server permissions on triggers - In a sql server 2008 r2 database I just added 3 triggers to a specific table. Can you tell me...

Cumulative sum based on criteria - Hey all, I am trying to go through this table (see attached), and then for every change in LOC_Zone, record the...

Transaction scope for a batch - Hi everyone, I wanted to know what is the transaction scope for the following batch. Does SQL Server commit the changes...

Finding patterns in rows (date ordered) - Dear All, Here's the background - I have a table of staffids and the shifts/durations worked - with usually 1 shift worked per...

Link server locking tables - hello SQL world- i am trying to copy tables form server A to server B using a link server. is there...

Issue with crypt_gen_random - This is my scalar function, which returns numbers between @min and @max (both included): [code] create function GetRandom(@min int, @max int) returns int as begin declare...

split string to three parts - I have a comma delimited string that's made up of 3 parts: Word1, Word2, Word3 I want to spli the string and...

Grouping timespans by the avg time per weekday - Hi There Long time - no see. I would appreciate a bit of help on a query I have to write. I work...

SQL Server 2008 : SQL Server Newbies

execution plan window - I am trying to observe the graphic execution plan, but my query is big. Is there any way that I...

Help to diagnose LCK_M_IX Query - Hi all, I have a query that is stuck (running for few hours but normally takes 2-5 mins).. Id like...

SQL Server 2008 : SQL Server 2008 High Availability

SQL Server 2008 R2 shutdown / startup procedure - I have a SQL Server 2008 R2 2-node cluster running Windows Enterprise 2008 R2 and SQL Server standard Edition 2008...

Failover Cluster Upgrade 64Bit - Hi everyone, we had to Upgrade RAM to our Servers running on Windows Server 2008 32 Bit. In Order to use...

Cluster Validation fails to check Storage - Hi All, I am getting an error while validating the cluster. Writing to sector 11 on disk with identifier 589e0435 from...

Need suggestions on replication - We are implementing a new architecture for one our modules. There are 100 tables in the database and out of...

SQL Server 2008 : SQL Server 2008 Administration

Sanity Check - Ola Hallengren Job sheduling - Hi, I am setting up Ola's SQL Server Maintenance solution and I am not entirely sure about the setting up of...

job token - Dear Experts How to use token in sql agent job or alert to send data to operator about alert like database...

Handshake error without specifying SSPI? - Hi There, I use a connection string as follows to connect to my SQL Server: [code="other"]Data Source=IPADDRESS;Initial Catalog=DATABASENAME;Persist Security Info=True;User ID=USER;Password=PASSWORD;Asynchronous Processing=True;MultipleActiveResultSets=True;Connection...

BCP Error - I am using XML format file to import CSV files using BCP. I use the following syntax. [quote]bcp dbname.dbo.tablename in "c:\files\test.csv" /T...

Avamar Transaction Log Backup Issue - Hi, We just recently started using Avamar to backup everything including an hourly SQL server transaction log backup. The issue we’re...

Creating a new Management Data Warehouse and removing the old one - When I took on the Role of DBA, i inherited some leftovers from the previous DBA. One such leftover was...

Registered Server List - Hi All! Is it common practice to keep an current Registered Server List in a central location for members of the...

Database Mail Stopped Working - Database Mail on our production server was working fine until about mid-morning today. Nothing has changed on the server that...

Existing Table division - I have a table "Claim" which contains 85+ columns. records in thousand(Not High-transactional table). The data get populated with the...

SQL08R2 Database Mail - I am so ready to scream, I set up DB mail and restarted the SQL Agent services but it fails...

Low Performance - High CPU Problem and others... - Hello everyone, I got the Problem, that my SQL Server has always 97-99% CPU usage. We have snapshot isolation mode enabled,...

Career : Certification

70-461 - Hi, Id like to take this exam and looking for some good prep.. My T-SQL skills are basic at present...

Microsoft Certified Master - There's been a lot of debate recently about whether certifications are worth doing and whether they provide you or your...

Career : Employers and Employees

Preparing for a job interview... - So I've gotten to the point where I absolutely hate my job. My company has been bought out (via a...

How to deal with boastful colleagues? - I've a colleague who is driving me insane. This person interferes in every conversation, and boasts himself. When I speak to...

SQLServerCentral.com : Anything that is NOT about SQL!

Features of NO SQL (may be mongo db) vs SQL Server - I want to hear some comments on "Featurs of NO SQL" from the mouth of actual sql developers / sql world...

An oddball survey, seeing as I'm looking at a new car... - So, I'm car shopping for a second car and got curious what everyone else is driving. Some time back, I...

Employer asking for Social Media login? - I've never experienced this but found it bizarre. If I was ever asked to provide access to my social media...

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

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

Reporting Services : Reporting Services

How to implement this formula? - Hi All, This the formula they have written in crystal reports.How to implement this one in SSRS2008R2? Formula: Shared StringVar sNewBranch; StringVar sPrintMe := "yy"; if...

Identify All Reports That Have A Subscription Assigned??? (+ any underlying detail) - Hi EXCUSE MY NAIVETY IF THIS IS UNDER THE WRONG SECTION - Thanks New to the RS so bear with me please. If...

SSRS 2008R2 Data set Properties Error - I have a stored procedure which i have to design the report for this i have connected to the datasource...

Combing RS databases with other production databases - Curious what the industry norm is. Can / should i put my report databases on the same server / instance as other...

how to get the source code from .rdl file - Hi , i have a rdl file that is displaying report, i want the source code of that rdl file.(since i...

Data Warehousing : Integration Services

SSIS Package hangs when called from Windows Service - Hi All, I have an SSIS package that connects to an Oracle source using the OLEDB Source connector. I'm attempting to...

Converting Mstrix into table - Matrix B1_LL to B1_UL is Range of score ------- Test Sub Grade B1_LL B1_UL B2_LL B2_UL TestName-Math 4 240 335 336 354 TestName-Math 5 240 357 358 374 TestName-Math 6 361 362 378 379 TestName-Read 3 240 341 342 364 TestName-Read 8 240 372 373 389 another Matrix is Score Current Year B1 B2 B3 B1 9 11 13 B2 8 10 12 B3 7 9 11 First i need to create table out of...

Dynamically load CSV files To Sql Server Tables - Hi, I am writing a SSIS package of copying CSV files to SQL tables. Csv files are five in numbers...